Skip to Main Content

How do I query for the last time a users report was run?

Brian.BMay 17 2019 — edited May 20 2019

I am an Oracle DBA, but I don't have any experience writing Discoverer reports. We have hundreds of locked accounts that were

owned by people who no longer work at my company. I am trying to see if any of those reports are being used. I would like to both

identify which reports haven't been run in years and locked users whose reports have not been run in years. I could then clean up

old reports and clean up old users.

This is the query that I have so far. But from this I can't tell when the last time a users reports where run.

Thanks,

SELECT usr.username,                 usr.account_status,

       eul.cnt,

       TO_CHAR(eul.DOC_UPDATED_DATE, 'DD-MON-YYYY') report_update_date,

       TO_CHAR(usr.lock_date,        'DD-MON-YYYY') lock_date

  FROM dba_users       usr

INNER JOIN

     ( SELECT doc_created_by, COUNT(*) cnt,

              MAX(DOC_UPDATED_DATE) DOC_UPDATED_DATE

         FROM euladm.eul5_documents

        GROUP BY doc_created_by ) eul

    ON usr.username       = eul.doc_created_by

WHERE usr.account_status = 'LOCKED'

ORDER BY eul.DOC_UPDATED_DATE, usr.username;

Comments
Post Details