Skip to Content
avatar image
Former Member

How to track continuous failure of reports using auditing universe

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

Add comment
10|10000 characters needed characters exceeded

  • 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 Jyothirmayee A

    Hi Jyothi,

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

    Regards

    Dibyajyoti Pattnaik

  • 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

  • Get RSS Feed

4 Answers

  • Jul 11, 2017 at 07:37 AM
    Add comment
    10|10000 characters needed characters exceeded

  • Jul 11, 2017 at 02:42 PM

    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
    
    Add comment
    10|10000 characters needed characters exceeded

  • Jul 11, 2017 at 02:04 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 14, 2017 at 04:35 AM

    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+BI+4.2+SP3

    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!!!

    Add comment
    10|10000 characters needed characters exceeded