cancel
Showing results for 
Search instead for 
Did you mean: 

How to find list of reports that were refreshed during given date/time range?

Former Member
0 Kudos

We have Auditing turned ON in BOXI 3.1.

I am trying to find list of reports that were refreshed within a given time range. Some of these reports could have either finished refreshing or could still be running during the given date/time range (because of volume of records to be retrieved).

The reports that came out of the box using the activity universe doesn't seem to help with this. Is there a back end SQL that can be directly run against Auditor Schema? I see that AUDIT_DETAIL and other such tables are present in this schema. But not exactly sure which event type or which audit event will provide the information I am looking for.

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member182521
Active Contributor
0 Kudos

There is a complexity involved around this Sid. Let me explain. Audit reports can only record below metrics while you open/view/refresh/schedule a report.


- Start time of the activity

- Duration

If you want to identify set of reports ran/viewed/refreshed during a particular time interval you should always make use of the Start time along with it duration. All you need to do is to build a logic either starttime  of the activity has to be in your time interval or the summation of Starttime and Duration has to be in the specified time interval.

Hope I made it clear.

Thanks

Mani

Former Member
0 Kudos

Hi Mani,

As usual your postings are to the point and insightful. Thanks for the value you are adding to this forum.

I am looking at the canned report that's already available and basically it pulls all the events with event type ID as 19. (Please refer to the bottom of this message for the full SQL). 19 is for Document Refreshed. And then there is Start_Timestamp filter. So I am pulling in all reports that ran on that day for more than 5 minutes. Since I don't know when the user might have run the report (just before 2PM when it crashed or few hours before).

It brings in bunch of reports. Then I cross verified those report SQLs against 2 problem SQLs I found in AWR report that was generated. I couldn't quite get lucky.

So now my next step is to look at the Query Builder to see if there are any reports with the prompt value I am interested in. I am trying to use SI_WEBI_PROMPTS under SI_ProcessInfo. But I don't know how to navigate down the array so that I can say where SI_NAME is 'Enter Fiscal Year' and SI_VALUES 1 array value is 2015. QB is so twisted and looks like there is no easy way to find to narrow down to problem reports unless I start using SDKs.

Appreciate your advice.

Event_Type_ID    IN (11,9,19,41,43)

9 Document Saved

11 Document Retrieved

19 Document Refreshed

41 SQL Generated

43 Prompt Selected

SELECT

  AUDIT_EVENT.Event_ID,

  AUDIT_EVENT.Duration,

  DERIVED_DOCUMENTOPERATION_NAME.Detail_Text,

  AUDIT_EVENT.User_Name,

  SERVER_PROCESS.Server_FullName,

  DERIVED_OBJECT_PATH.Detail_Text,

  RTRIM(TO_CHAR(( LPAD(RTRIM(TO_CHAR(AUDIT_EVENT.Start_Timestamp,'YYYY'), CHR(0)),4,'0') ) || '-'|| ( LPAD(RTRIM(TO_CHAR(AUDIT_EVENT.Start_Timestamp,'MM'), CHR(0)),2,'0') ) || '-' || ( LPAD(RTRIM(TO_CHAR(AUDIT_EVENT.Start_Timestamp,'DD'), CHR(0)),2,'0') )),CHR(0)),

  DERIVED_DOCUMENTOPERATION_NAME.Object_Type,

  AUDIT_EVENT.Start_Timestamp

FROM

  SERVER_PROCESS INNER JOIN AUDIT_EVENT ON (SERVER_PROCESS.Server_CUID=AUDIT_EVENT.Server_CUID)

   INNER JOIN (

  select

  AUDIT_EVENT.Server_CUID, AUDIT_EVENT.Event_ID, AUDIT_EVENT.Event_Type_ID, RTRIM(To_CHAR(AUDIT_DETAIL.Detail_Text),CHR(0)) as Detail_Text

from

  AUDIT_EVENT, AUDIT_DETAIL

where

  (AUDIT_EVENT.Server_CUID = AUDIT_DETAIL.Server_CUID) and

  (AUDIT_EVENT.Event_ID = AUDIT_DETAIL.Event_ID) and

  (AUDIT_DETAIL.Detail_Type_ID = 43)

  )  DERIVED_OBJECT_PATH ON (AUDIT_EVENT.Event_ID=DERIVED_OBJECT_PATH.EVENT_ID and AUDIT_EVENT.Server_CUID=DERIVED_OBJECT_PATH.SERVER_CUID)

   INNER JOIN (

  select

  AUDIT_EVENT.Server_CUID, AUDIT_EVENT.Event_ID, AUDIT_DETAIL.Detail_Type_ID,  AUDIT_EVENT.Event_Type_ID,  EVENT_TYPE.Event_Type_Description,

AUDIT_EVENT.Object_Type, AUDIT_DETAIL.Detail_Text as Detail_Text

from

  AUDIT_EVENT, AUDIT_DETAIL, EVENT_TYPE

where

  (AUDIT_EVENT.Server_CUID = AUDIT_DETAIL.Server_CUID) and (EVENT_TYPE.Event_Type_ID= AUDIT_EVENT.Event_Type_ID) and

  (AUDIT_EVENT.Event_ID = AUDIT_DETAIL.Event_ID) and

(((AUDIT_EVENT.Event_Type_ID In (196609,196610) and AUDIT_DETAIL.Detail_Type_ID In (43,90,3)) or (AUDIT_EVENT.Event_Type_ID In (11,9) and

AUDIT_DETAIL.Detail_Type_ID In (43,90,8)) or (AUDIT_EVENT.Event_Type_ID = 43 and AUDIT_DETAIL.Detail_Type_ID In (43,90,8,23,24)) or (AUDIT_EVENT.Event_Type_ID In (41,22,19) and AUDIT_DETAIL.Detail_Type_ID In (43,90,8,19)) or (AUDIT_EVENT.Event_Type_ID In

(327681,327682,327683) and AUDIT_DETAIL.Detail_Type_ID In (43,90,3)) or (AUDIT_EVENT.Event_Type_ID In (327687,327688) and

AUDIT_DETAIL.Detail_Type_ID In (43,90,3,9,65,74,73,75,68,76,70,67,69))) and ((AUDIT_EVENT.Object_Type In('Crystal Reports','Web Intelligence','Desktop Intelligence'))))

  )  DERIVED_DOCUMENT_OPERATION ON (AUDIT_EVENT.Server_CUID=DERIVED_DOCUMENT_OPERATION.SERVER_CUID  AND  AUDIT_EVENT.Event_ID=DERIVED_DOCUMENT_OPERATION.EVENT_ID)

   INNER JOIN (

  select

  AUDIT_EVENT.Server_CUID, AUDIT_EVENT.Event_ID, AUDIT_EVENT.Object_Type, RTRIM(To_CHAR(AUDIT_DETAIL.Detail_Text),CHR(0)) as Detail_Text

from

  AUDIT_EVENT, AUDIT_DETAIL

where

  (AUDIT_EVENT.Server_CUID = AUDIT_DETAIL.Server_CUID) and

  (AUDIT_EVENT.Event_ID = AUDIT_DETAIL.Event_ID) and

  (((AUDIT_EVENT.Event_Type_ID In (196609,196610,327681,327683,327682,327687,327688) and AUDIT_DETAIL.Detail_Type_ID =3) or (AUDIT_EVENT.Event_Type_ID In (11,9,19,41,43) and AUDIT_DETAIL.Detail_Type_ID =8)) and (AUDIT_EVENT.Object_Type In ('Crystal Reports','Web Intelligence','Desktop Intelligence')))

  )  DERIVED_DOCUMENTOPERATION_NAME ON (DERIVED_DOCUMENT_OPERATION.SERVER_CUID=DERIVED_DOCUMENTOPERATION_NAME.SERVER_CUID  AND  DERIVED_DOCUMENT_OPERATION.EVENT_ID=DERIVED_DOCUMENTOPERATION_NAME.EVENT_ID)

 

WHERE

  (

   AUDIT_EVENT.Start_Timestamp  >=  '26-08-2015 00:00:00'

   AND

   AUDIT_EVENT.Event_Type_ID  =  19

   AND

   AUDIT_EVENT.Duration  >=  300

  )

former_member182521
Active Contributor
0 Kudos

Agree. In such scenarios we need investigate further with Auditor and CMS database.