Question on Reporting on SAP BO Audit Universe

[Former Member]

Hi,

I have built a report on Audit Universe, to fetch the failed schedules list. This report is not capturing when the error message is "Object could not be scheduled within the specified time interval". Please help me how to fetch this error and let me know under which field is this error message is getting captures in Audit DB.

Thanks in advance

SQL used in the report is as follows:

SELECT Table__9.USER_NAME, Table__1.ADDED_TO_ADS, count(distinct Table__1.EVENT_ID), max( Table__11.OBJECT_TYPE_NAME ), max( Clusters.CLUSTER_NAME ), Table__8.SERVER_NAME, ( sum(to_number(dbms_lob.substr(Table__13.EVENT_DETAIL_VALUE,dbms_lob.getlength(Table__13.EVENT_DETAIL_VALUE)-1))/1024) )/1024, max( Table__6.APPLICATION_TYPE_NAME ), max( Table__5.EVENT_CATEGORY_NAME ), max( Table__4.EVENT_TYPE_NAME ), Table__1.OBJECT_FOLDER_PATH, dbms_lob.substr(Table__18.EVENT_DETAIL_VALUE,dbms_lob.getlength(Table__18.EVENT_DETAIL_VALUE)-1), max( CASE Table__1.STATUS_ID WHEN 1 THEN 'Failed' ELSE 'Successful' END ), dbms_lob.substr(Table__16.EVENT_DETAIL_VALUE,dbms_lob.getlength(Table__16.EVENT_DETAIL_VALUE)-1), Table__1.OBJECT_NAME, Table__1.START_TIME FROM ADS_EVENT_TYPE_STR Table__4 INNER JOIN ADS_EVENT_TYPE Table__3 ON (Table__4.EVENT_TYPE_ID=Table__3.EVENT_TYPE_ID AND upper(substr(Table__4.LANGUAGE,0,2))=upper(substr(@Variable('PREFERRED_VIEWING_LOCALE'),0,2))) INNER JOIN ADS_EVENT Table__1 ON (Table__3.EVENT_TYPE_ID=Table__1.EVENT_TYPE_ID) LEFT OUTER JOIN ADS_EVENT_DETAIL Table__16 ON (Table__16.EVENT_ID=Table__1.EVENT_ID AND Table__16.EVENT_DETAIL_TYPE_ID=1) LEFT OUTER JOIN ADS_USER Table__9 ON (Table__9.USER_ID = Table__1.USER_ID AND Table__9.CLUSTER_ID = Table__1.CLUSTER_ID) INNER JOIN ADS_STATUS_STR Table__7 ON (Table__7.STATUS_ID = Table__1.STATUS_ID AND Table__7.EVENT_TYPE_ID = Table__1.EVENT_TYPE_ID AND upper(substr(Table__7.LANGUAGE,0,2))=upper(substr(@Variable('PREFERRED_VIEWING_LOCALE'),0,2))) LEFT OUTER JOIN ADS_EVENT_DETAIL Table__18 ON (Table__18.EVENT_ID=Table__1.EVENT_ID AND Table__18.EVENT_DETAIL_TYPE_ID=11) LEFT OUTER JOIN ADS_EVENT_DETAIL Table__13 ON (Table__13.EVENT_ID=Table__1.EVENT_ID AND Table__13.EVENT_DETAIL_TYPE_ID=17) INNER JOIN ADS_CLUSTER_STR Clusters ON (Clusters.CLUSTER_ID=Table__1.CLUSTER_ID AND upper(substr(Clusters.LANGUAGE,0,2))=upper(substr(@Variable('PREFERRED_VIEWING_LOCALE'),0,2))) LEFT OUTER JOIN ADS_OBJECT_TYPE_STR Table__11 ON (Table__11.OBJECT_TYPE_ID=Table__1.OBJECT_TYPE_ID AND upper(substr(Table__11.LANGUAGE,0,2))=upper(substr(@Variable('PREFERRED_VIEWING_LOCALE'),0,2))) INNER JOIN ADS_SERVER_NAME_STR Table__8 ON (Table__8.CLUSTER_ID = Table__1.CLUSTER_ID AND Table__8.SERVER_ID = Table__1.SERVER_ID AND upper(substr(Table__8.LANGUAGE,0,2))=upper(substr(@Variable('PREFERRED_VIEWING_LOCALE'),0,2))) LEFT OUTER JOIN ADS_APPLICATION_TYPE_STR Table__6 ON (Table__6.APPLICATION_TYPE_ID=Table__1.CLIENT_TYPE_ID AND upper(substr(Table__6.LANGUAGE,0,2))=upper(substr(@Variable('PREFERRED_VIEWING_LOCALE'),0,2))) INNER JOIN ADS_EVENT_CATEGORY_STR Table__5 ON (Table__5.EVENT_CATEGORY_ID=Table__3.EVENT_CATEGORY_ID AND upper(substr(Table__5.LANGUAGE,0,2))=upper(substr(@Variable('PREFERRED_VIEWING_LOCALE'),0,2))) WHERE ( ( Table__1.STATUS_ID=1 ) AND Table__7.STATUS_NAME IN ( 'Job Failed','Delivery Failed','Logon Failed','Operation Failed','Job Cancelled','Deletion Failed','Logout Failed','Prompt Failed','Save Failed','Refresh Failed','Cube Connection Failed','Retrieved Failed' ) AND Table__1.EVENT_TYPE_ID IN ( 1011 ) AND Table__11.OBJECT_TYPE_ID NOT IN ( 'AcNs5braeoJIlf813RpnZFs' ) AND dbms_lob.substr(Table__16.EVENT_DETAIL_VALUE,dbms_lob.getlength(Table__16.EVENT_DETAIL_VALUE)-1) NOT IN ( 'login error. [530 User cannot log in.]: [CrystalEnterprise.Ftp]','login error. [530 User catalin cannot log in. ]: [CrystalEnterprise.Ftp]','login error. [530 User anonymous cannot log in. ]: [CrystalEnterprise.Ftp]','login error. [530 User cannot log in. ]: [CrystalEnterprise.Ftp]','Enterprise authentication could not log you on. Please make sure your logon information is correct. (FWB 00008)','Concurrent user limit reached','All of your system''s 0 Concurrent Access Licenses are in use at this time or your system''s license key has expired. Try again later or contact your administrator to obtain additional licenses. (FWB 00014)','Objects may not be scheduled with your current license keys. Please purchase a license key that enables scheduling.','No valid license key found','Incorrect password.' ) AND Table__9.USER_NAME NOT IN ( 'Administrator') ) GROUP BY Table__9.USER_NAME, Table__1.ADDED_TO_ADS, Table__11.OBJECT_TYPE_ID, Clusters.CLUSTER_ID, Table__8.SERVER_NAME, Table__6.APPLICATION_TYPE_ID, Table__5.EVENT_CATEGORY_ID, Table__4.EVENT_TYPE_ID, Table__1.OBJECT_FOLDER_PATH, dbms_lob.substr(Table__18.EVENT_DETAIL_VALUE,dbms_lob.getlength(Table__18.EVENT_DETAIL_VALUE)-1), Table__7.STATUS_ID, dbms_lob.substr(Table__16.EVENT_DETAIL_VALUE,dbms_lob.getlength(Table__16.EVENT_DETAIL_VALUE)-1), Table__1.OBJECT_NAME, Table__1.START_TIME