BusinessObjects SQLs

Auditing & Report Usage

Example

Long running queries: RUN = Scheduled Refresh ; REFRESH = Interactive Refresh

-- Long running queries: RUN = Scheduled Refresh ; REFRESH = Interactive Refresh
SELECT f.folderpathcomplete AS document_folder
    ,v.name AS document_name
    ,v.cmsid
    ,COUNT(a.event_id) nbr_runs
    ,ROUND(((AVG(refresh_ms)) / 1000), 2) avg_refresh_sec
    ,ROUND(((AVG(run_ms)) / 1000), 2) avg_run_sec
FROM audit_summary2 a
    ,documents v
    ,v_folders2a f
WHERE a.object_cuid = v.cuid
    AND a.CMSID = v.cmsid --AND REFRESH_MS > 10000 -- refresh greater than 100 seconds
    AND a.audit_date_time > CURRENT_DATE - INTERVAL '90 days'
    AND f.cmsid = v.cmsid
    AND f.cuid = v.parentfoldercuid
    AND a.event_type_id IN (
        1011
        ,1003
        )
GROUP BY v.name
    ,v.cmsid
    ,f.folderpathcomplete
ORDER BY nbr_runs DESC