Last Run Time for Reports

server administration XI Server Discussion
1

Hi,

I am trying to figure out all reports that have not been refreshed since May 01 ,2009 in my repository. Since we do not have Auditor installed I am querying the CI_INFOOBJECTS table through Query Builder. The following is my query -

SELECT
SI_NAME,
SI_LAST_RUN_TIME ,
SI_AUTHOR,
SI_INSTANCE,
SI_PARENTID,
SI_PARENT_FOLDER,
SI_KIND
FROM
CI_INFOOBJECTS
WHERE
SI_KIND IN ('Webi','FullClient')
AND SI_LAST_RUN_TIME<'2009/05/01,00:00:00'
AND SI_PARENTID =  &amp;s

Now I modified the query to include SI_LAST_RUN_TIME IS NULL and My report count increased. I just want to figure out what it means when there is no value for SI_LAST_RUN_TIME for a report object. Does it mean that the report was never successfully refreshed in InfoView? Any pointers, anyone?

[Moderator Edit: Added code formatting - Jansi]

When posting code samples please use the code option for formatting. It will preserve any indenting or formatting that you may have done.

vinod_menon :india: (BOB member since 2007-04-09)

2

The last run time shows the time a scheduled instance based on that document was executed. The wording is a bit clumsy in my opinion, but it is what it is.

BoB LoblaW :us: (BOB member since 2007-10-23)

3

If that is so, how do you explain results I get for the following query -


SELECT
SI_NAME,
SI_LAST_RUN_TIME ,
SI_KIND
FROM
CI_INFOOBJECTS
WHERE
SI_KIND IN ('Webi','FullClient') AND [b] SI_INSTANCE='false'[/b]

Now, I am really confused :crazy:

vinod_menon :india: (BOB member since 2007-04-09)

4

Last run time is a property of the template document, not the instance. Although it is attached to the template, the value is derived from its children, i.e. scheduled instances.

BoB LoblaW :us: (BOB member since 2007-10-23)

5

Okay. I ll get to my basic requirement. I want the last run time (last time of refresh) of all web intelligence and desk intelligence reports, which may or may not have scheduled instances, in my repository. Is there any attribute associated with the report object(s) anywhere in the CMS database that I can query to get this information?

PS - I do not have Auditor installed.

vinod_menon :india: (BOB member since 2007-04-09)

6

I’m afraid not then. I’ve never checked this, but perhaps the si_update_ts (last update time) changes when a report is refreshed.

BoB LoblaW :us: (BOB member since 2007-10-23)

7

No. SI_UPDATE_TS is the creation time of the latest instance associated with that report. It does not change after a refresh.

vinod_menon :india: (BOB member since 2007-04-09)

8

Yes. last modified time and run time are different as shown in Infoview and CMC.

If you check CMC, the last modified time of the report is the run time of its lastest instance.

Where as Infoview exactly shows last run-time.

I am afraid that you will have to do some manual work but you can get the perfect information using Infoview >> Advance Search Options >> Search by time.

Information you will get is: Name, path, owner, last_run_time, instnaces.

swap_bo (BOB member since 2009-05-01)