How to track continuous failure of reports using auditing universe

[Former Member]

I want to create a BO report using auditing universe which will track:-

1.Which report has failed 3 times successively without a success instance between the 3 failures.

2.Create a publication that will trigger me a email,which are the reports that has failed 3 times successively.

Thanks in Advance.

Regards

Dibyajyoti Pattnaik

[jyothirmayee_s]

Hi,

Make sure to verify if Audit DB is enabled.

https://archive.sap.com/documents/docs/DOC-53904

Thanks,

Jothi

[Former Member]

Hi Jyothi,

Yes,Audit DB is enabled.

Regards

Dibyajyoti Pattnaik

[jyothirmayee_s]

Hi,

Then the above link has some sample Audit reports on top of universe.

You can have look at those samples.

Thanks,

Jothi

[Former Member]

Hi Jyothi,

I want to build a BO report using objects available in auditing universe

Regards

Dibyajyoti Pattnaik

[jyothirmayee_s]

Hi,

Do you have Universe created on Audit DB? and need steps to create WebI reports on the Audit Universe?,

You can get the same report by Query Builder too.

If I have to create a WebI report and schedule to myself everyday to view the activity then, I would go with the below objects first:

• Action Name from Actions, Scheduling Status(Fail/Success)
• Server Kind from Server Information
• Object Name from Actions/ Action Details/, which is equivalent to Report Name
• Action Time from Actions/Action Time , This can be Query filter - supposedly 6 or 8am Weekday.
• Action Duration from Actions , gives you no of secs the report took to run
• Action Type from Actions
• Action Type Details from Actions/Action Type
• User Name from Actions, who ran that report.
• Action ID from Actions

Thanks,

Jothi

[Former Member]

HI Dibyajyoti,

In BI4.2 SP2 SAP has introduced a Universe which they built on top of CMS Database.

Please see the below link:

https://wiki.scn.sap.com/wiki/display/BOBJ/Unlock+the+CMS+database+with+new+data+access+driver+for+B...

ON top of this universe create a Webi report report,filter it out on Schedule status=3(Failed instances) and then create a variable on a updatedTime stamp Dimension on universe (use Count function).

Here one of the advantage is you will get the live data compared to auditing.

Hope it helps!!!

[Joe_Peters]

I don't think it can be done with the out-of-the-box universe, but the following Oracle will will produce reports that failed three times consecutively.

with a as 
(
    select
        start_time,
        user_name,
        object_name,
        object_folder_path,
        status_name,
        lag (status_name,1,null) over (partition by object_folder_path order by start_time) prev_status,
        lag (status_name,2,null)  over (partition by object_folder_path order by start_time) prev_status_2,
        lag (start_time,1,null) over (partition by object_folder_path order by start_time) prev_time,
        lag (start_time,2,null)  over (partition by object_folder_path order by start_time) prev_time_2
    from
        ads_event ae
        join ads_event_type_str et
            on ae.event_type_id = et.event_type_id
            and et.language = 'EN'    
        join ads_status_str st
            on ae.status_id = st.status_id
            and st.language = 'EN'
            and st.event_type_id = ae.event_type_id
    where
        start_time > sysdate - 30
        and event_type_name = 'Run'
)
select
        user_name,
        object_name,
        object_folder_path,
        status_name,
        start_time,
        prev_time,
        prev_time_2
from
	a
where
	status_name = 'Job Failed'
    and prev_status = 'Job Failed'
    and prev_status_2 = 'Job Failed'            
order by
	object_folder_path,
    start_time desc

[kohesco]

Hi,

Crystal reports enables you to broadcast alerts on a condition. so you can use the WebI document as datasource of your CR.

Grtz

Koen

[former_member230921]

Please try this.

https://blogs.sap.com/2015/07/15/unlock-the-auditing-database-with-a-new-universe-and-web-intelligen...