BusinessObjects Board

Utility to find active Reports

other Auditor
#1

Hi,

Would like to know is there is any utility or excel macro or any other solution available to find the active reports (last 6 months) from the repository.
We have more than 50 K reports and 400 universes and it is tedious to get this through Audit reports.

sat.dpi :india: (BOB member since 2009-02-12)

#2

I donā€™t know of any utilities as such. I have written code using the SDK that will do this sort of thing - it provides a full list of all reports and then reads the audit database to determine which have been used. Unfortunately, I canā€™t share that code as it belongs to my employer. I just mention it as a possibility for you to code.

-Dell

hilfy :us: (BOB member since 2007-04-16)

#3

Hi,

it would be great if you can provide the code to get the list it will be really helpful.

Regards
Navneet Kaur

Navneet Kaur (BOB member since 2015-11-18)

#4

I canā€™t post the code, but here are the steps you need to take after connecting to both the BO system and the CMS database:

  1. Get a list of all of the folders in the root of Public Folders (for XI 3.1 and older: SI_PARENTID = 0; for 4.x: SI_PARENTID = 43): Select SI_ID, SI_NAME from CI_INFOOBJECTS where SI_KIND = ā€˜Folderā€™ and SI_PARENTID = <0 or 43 depending on system version>

  2. For each folder, call ā€œloadFolder(SI_ID of folder)ā€.

  3. loadFolder(SI_ID) This is a recursive method that walks the folder tree to get reports

    a. Get the list of subfolders:
    Select SI_ID, SI_NAME from CI_INFOOBJECTS where SI_KIND =
    ā€˜Folderā€™ and SI_PARENTID = SI_ID of parent folder

    b. Get the list of reports from the folder:
    Select SI_ID, SI_CUID, SI_NAME from CI_INFOOBJECTS where
    SI_KIND <> 'Folder and SI_PARENTID = SI_ID of parent folder
    and SI_INSTANCE = 0

    c. For each report, call getLastRun(SI_CUID of report) to get the last used date from the audit database.

  4. lgetLastRun(SI CUID): This method gets the last used date for a report template. I donā€™t have access to an audit database for 3.1, which has a different structure, but hereā€™s the query I use for 4.x:

Select max(Start_Time) as lastRun
from ADS_EVENT
where Object_ID =
and Event_Type_ID in (1002, 1003, 1007, 1011)

Event Types
1002 = View
1003 = Refresh
1007 = Modify
1011 = Run

  1. If the date from getLastRun is less than 6 months ago, output the report name and last run. You can also track the folder itā€™s in through the recursive calls to loadFolder().

  2. Repeat the process for reports in User Folders using this query to get that list:

Select SI_ID from CI_INFOOBJECTS where SI_NAME = ā€˜User Foldersā€™

Use this SI_ID as the SI_PARENTID value to get the user favorites folders. NOTE: If you have more than 1,000 users, youā€™ll have to use something like: Select Top 10000 SI_ID, SI_NAMEā€¦ SDK queries will only return up to 1,000 records. Using ā€œSelect Top Xā€ will get around that limit, but it will also take longer for the query to run.

-Dell

hilfy :us: (BOB member since 2007-04-16)