BusinessObjects Board

Audit (ADS_EVENT) - Get Document/Report Name for an Instance?

other Auditor
#1

Hi,
I have a query against ads_event (among others). The Object_Name returned is often an Instance Name rather than the originating rpt/doc name. Any info out there on how to get the originating rpt/doc name for those Instance Names?
Thanks!

SELECT
  ADS_EVENT.Object_Name,
  case when max("TOP_FOLDER"."TOP_FOLDER_NAME") in ('EBI Console','EBI Team','GHX Public Ad-Hoc Reports','GHX Public Official Reports','LCM','Official Customer Distributed Reports','Oracle Apps') 
         then max("TOP_FOLDER"."TOP_FOLDER_NAME")
         else 'User Personal Folder'
  end,
  COUNT(*),
  max(ADS_EVENT.Start_Time),
  count( distinct ADS_EVENT.User_Name )
FROM
  ( 
  SELECT F1.Object_ID, MAX(F1.Object_Name) TOP_FOLDER_NAME
FROM ADS_EVENT F1 INNER JOIN ADS_OBJECT_TYPE_STR O1 ON F1.Object_Type_ID = O1.Object_Type_ID AND O1.Language = 'EN'
WHERE O1.Object_Type_Name LIKE '%Folder%'
GROUP BY F1.Object_ID
  )  "TOP_FOLDER" RIGHT OUTER JOIN ADS_EVENT ON (ADS_EVENT.Top_Folder_ID="TOP_FOLDER".Object_ID)
   LEFT OUTER JOIN ADS_STATUS_STR ON (ADS_EVENT.Status_ID=ADS_STATUS_STR.Status_ID and ADS_EVENT.Event_Type_ID=ADS_STATUS_STR.Event_Type_ID  AND  ADS_STATUS_STR.Language='EN')
   LEFT OUTER JOIN ADS_EVENT_TYPE_STR ON (ADS_EVENT.Event_Type_ID=ADS_EVENT_TYPE_STR.Event_Type_ID  AND  ADS_EVENT_TYPE_STR.Language='EN')
WHERE
  (
   ADS_EVENT.Object_Name  <>  ''
   AND
   ADS_EVENT_TYPE_STR.Event_Type_Name  IN  ( 'View','Run','Refresh'  )
   AND
   ADS_STATUS_STR.Status_Name  NOT IN  ( 'Job Failed','Refresh Failed','View Failed'  )
  )
GROUP BY
  ADS_EVENT.Object_Name
#2

When the object is an instance, the parent report name will be the last entry in object_folder_path. For example, you have a report named “my report” that you schedule with a name of “my instance”. The events should have the following values:

OBJECT_NAME: my_instance
OBJECT_FOLDER_PATH: /reports folder/another folder/another folder/my report/

1 Like