2 Replies Latest reply on May 20, 2019 5:41 PM by Brian.B

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

    Brian.B

      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;