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;

sbeck-Oracle

It is not supported to access the underlying EUL tables directly.  The simplest method is to install the EUL Status Workbooks.  See the Discoverer Administrators Guide in chapter 19.  Oracle supplies the BA and sample workbooks.
  Admin guide:  https://docs.oracle.com/cd/E28280_01/bi.1111/b32519/eul_status.htm#BIDAG739

Please be aware that statistics are gathered when a User exits the Discoverer session.  If your users use the X to "terminate" the window, this is not an exit and no statistics are written.

Regards,

Sharon

Premier support for Discoverer 11g ended June 2014.  Version 11.1.1.7.0 is the final / terminal release of the Discoverer product.  Discoverer is under sustaining support.

Note 1634827.1 - Oracle Business Intelligence Discoverer Statement of Direction - March 2014

Brian.B

Thanks for the information. Our developers are working on replacing Discoverer. So while I appreciate that it makes sense to use an Oracle provided interface when one is available, I mostly wanted to show which users were locked and have not had any reports that they own run in over a year.