Forum Stats

  • 3,814,639 Users
  • 2,258,893 Discussions
  • 7,892,792 Comments

Discussions

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

Brian.B
Brian.B Member Posts: 56 Red Ribbon
edited May 20, 2019 1:41PM in Discoverer

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;

Answers

  • sbeck-Oracle
    sbeck-Oracle Member Posts: 116 Employee
    edited May 17, 2019 2:56PM

    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
    Brian.B Member Posts: 56 Red Ribbon
    edited May 20, 2019 1:41PM

    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.