BusinessObjects Board

When Was a Universe Last Uploaded ?

security Supervisor (4.x - 6.x)
#1

Hello Everyone,

 Is there a way to find out when a universe was last uploaded to the repository ? if say I have a number of developers working (BO 6.5.1) and would like to know when was the most recent upload of a given universe to the repository took place, how do I find that out ? (even better if I can also tell which username, but that is probably wishing for too much !). I know there is BO Auditor and so on, but I am wondering if there is a method without using this tool. Thanks in advance.

Regards,
Veronica

Veronica (BOB member since 2002-11-22)

#2

Hi Veronica,

to get that last-export date for the universe you can query BO repository table OBJ_M_UNIVERSES. For BO repository sitting on the Oracle DB you can use this SQL:


SELECT m_uni_c_filename AS uni_filename,
       m_uni_c_longname AS uni_longname,
       TO_DATE ('15-12-1970', 'dd-mm-yyyy') + m_uni_n_lut / 86400 AS last_export_date
  FROM obj_m_universes

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#3

Hi Veronica,

Here is the query you ask for :wink:

Note that under the repo the name of the user who makes the export is not stored !!!

Oracle repo SQL under sec domain :


SELECT
  OBJ_M_UNIVERSES.M_UNI_C_FILENAME,
  to_date('15/12/1970','DD/MM/YYYY') + (OBJ_M_UNIVERSES.M_UNI_N_LUT/86400),
  OBJ_M_REPOSITORY.M_REPO_C_NAME
FROM
  OBJ_M_UNIVERSES,
  OBJ_M_REPOSITORY
WHERE
  ( OBJ_M_REPOSITORY.M_REPO_N_ID=OBJ_M_UNIVERSES.M_UNI_N_REPOID and OBJ_M_REPOSITORY.M_REPO_N_TYPE=2  )
  AND  ( OBJ_M_REPOSITORY.M_REPO_N_TYPE=2  )

Regards

#4

Thank you both so much for these responses, but unfortunately our repository sits on an MS SQL server :frowning: , do you have enquivalent script for this ? if not, I guess I can dig up an MS SQL book and look at the differences… but again thank you both for this !.

Regards,
Veronica

Veronica (BOB member since 2002-11-22)

#5

I use this on SQL Server but query different tables.
SELECT
UNV_UNIVERSE.UNIVERSE_ID,
UNV_UNIVERSE.UNI_LONGNAME,
UNV_AUDIT.AUD_UNIVERSION,
UNV_UNIVERSE.UNI_MODIFYDATE
FROM
UNV_UNIVERSE,
UNV_AUDIT
WHERE
( UNV_AUDIT.UNIVERSE_ID=UNV_UNIVERSE.UNIVERSE_ID )

Michele Pinti (BOB member since 2002-06-17)

#6

This is wonderful thank you Michele, I have 2 questions :

  1. Does the script show when the universe was last edited or when it was exported ? (so if I import and immediate re-export the universe to the repository, does it make an entry in this table ? or only when I edit the universe and export it ?).

  2. Is there a way to show the time the universe was exported, as well as the date ?.

Thank you,
Veronica

Veronica (BOB member since 2002-11-22)

#7
  1. This shows when the universe was last exported. There isn’t any info in the repository to show when it was edited since this is done ‘outside’ of the repository.
  2. This table does not have timestamps.

Michele Pinti (BOB member since 2002-06-17)

#8

Here is the SQL from Goiffon and Smith converted to run on SQL Server

SELECT 
  OBJ_M_UNIVERSES.M_UNI_C_FILENAME, 
  DateAdd(ss, (OBJ_M_UNIVERSES.M_UNI_N_LUT  - 14400) ,  Cast('1970-12-15 00:00:00' as datetime)) as Export_Time,  
  OBJ_M_REPOSITORY.M_REPO_C_NAME 
FROM 
  OBJ_M_UNIVERSES, 
  OBJ_M_REPOSITORY 
WHERE 
  ( OBJ_M_REPOSITORY.M_REPO_N_ID=OBJ_M_UNIVERSES.M_UNI_N_REPOID and OBJ_M_REPOSITORY.M_REPO_N_TYPE=2  ) 
  AND  ( OBJ_M_REPOSITORY.M_REPO_N_TYPE=2  )

Michele Pinti (BOB member since 2002-06-17)

#9

Ah thank you so much Michele ! this is exactly what’s needed :mrgreen: perfect !! :yesnod:

Thanks,
Veronica

Veronica (BOB member since 2002-11-22)

#10

You might want to look here for additional documentation if you haven’t already.

Michele Pinti (BOB member since 2002-06-17)

#11

Hi,

It’s better to query OBJ_M tables in the sec domain if you have several universe domain. If you run your query using UNV_ tables you need to do this in all the schemas where you store universe domain.

Regards

#12

Well you can find out last updated date & the user name that updated it without executing any query

  1. Import universe
  2. Go to summary tab in universe parameters dialog box

You get all information you need.
Probably the easiest way to find this out.

stiwari :india: (BOB member since 2005-08-23)