This discussion is archived
7 Replies Latest reply: May 27, 2009 8:47 AM by 701988 RSS

How to manage Discoverer Active Users?

701988 Newbie
Currently Being Moderated
Hi,

How can I manage Discoverer active users.
What steps should be followed to clean up and delete their unused reports.

I need to delete all inactive users(no activity in past 6 months) and their reports(if it's not shared by an active user).

Thanks
Visakh
  • 1. Re: How to manage Discoverer Active Users?
    Kranthi.K Guru
    Currently Being Moderated
    Hi visakh,

    You can delete the access of the report and the user who are no longer active using the following
    go to discoverer desktop or plus and File/Manage Workbooks/Sharing

    Here you can delete the users who are in shared,so no longer the user can be active.In the other tab you can delete
    the workbook assigned to the user i.e. deactive workbooks.

    Hope this answers your question


    Regards,
    Kranthi
  • 2. Re: How to manage Discoverer Active Users?
    701988 Newbie
    Currently Being Moderated
    Hey,
    Thanks for the reply.

    I have 600+ users and i need to find the users who are not active for past 6 months

    In the backend:
    Is it possible by querying the discoverer log in discoverer tables.
    like '<user>.EUL5_DOCUMENTS' and get the last access date??
  • 3. Re: How to manage Discoverer Active Users?
    Michael Armstrong-Smith Oracle ACE
    Currently Being Moderated
    Hi Visakh
    Let me explain a little about the behind the scenes workings of the EUL. First of all, let's look at EUL5_DOCUMENTS

    Some of the important columns in there are:
    DOC_ID - this is used for cross-referencing throughout the EUL
    DOC_NAME - this is the workbook name
    DOC_CREATED_DATE - this is the date the workbook was first saved to the database
    DOC_UPDATED_DATE - this is the date the workbook was last saved to the database - it is not the the date it was last run
    NOTM - this stands for Number Of Times Modified and is just a counted of the number of times the workbook has been saved to the database - some companies use this for version control

    As you can see, there is nothing within EUL5_DOCUMENTS that records when it was last used.

    To get this information, assuming you have been gathering statistics, you need to look in the EUL5_QP_STATS table.

    Some of the important columns in there are:
    QS_DOC_NAME - this is the workbook name
    QS_DOC_DETAILS - this is the worksheet name
    QS_DOC_OWNER - this is the owner of the workbook
    QS_CREATED_BY - this is the name of the user who ran the worksheet
    QS_CREATED_DATE - this is the date and time when the worksheet was run

    Therefore, the information you are looking for can be found using a combination of these tables. This script may help:

    SELECT
    QPP.QS_DOC_NAME      WORKBOOK_NAME,
    QS_DOC_OWNER WORKBOOK_OWNER,
    MAX(QPP.QS_CREATED_DATE)  CREATED_DATE,
    QPP.QS_CREATED_BY     OWNER
    FROM
    EUL5_QPP_STATS QPP,
    EUL5_DOCUMENTS DOCS
    WHERE
    DOCS.DOC_NAME = QPP.QS_DOC_NAME
    GROUP BY
    QPP.QS_CREATED_BY,
    QPP.QS_DOC_NAME
    ORDER BY 1,2,3;

    You can also add a further WHERE clause to restrict the QS_CREATED_BY to being, for example, prior to the start of the year, like this:

    SELECT
    QPP.QS_DOC_NAME      WORKBOOK_NAME,
    QS_DOC_OWNER WORKBOOK_OWNER,
    MAX(QPP.QS_CREATED_DATE)  CREATED_DATE,
    QPP.QS_CREATED_BY     OWNER
    FROM
    EUL5_QPP_STATS QPP,
    EUL5_DOCUMENTS DOCS
    WHERE
    DOCS.DOC_NAME = QPP.QS_DOC_NAME
    AND QPP.QS_CREATED_DATE < '01-JAN-2009'
    GROUP BY
    QPP.QS_CREATED_BY,
    QPP.QS_DOC_NAME
    ORDER BY 1,2,3;

    You might wonder why EUL5_QPP_STATS does not make reference to the DOC_ID. This is because workbooks don't have to be saved to the database. They could be saved to the file system if the user is using Desktop. There also won't be an ID generated if the workbook was an ad-hoc workbook created during a Discoverer session but never saved. Seeing as you are interested in knowing which reports have not been used for a period of time you indeed need to join to EUL5_DOCUMENTS as per my script.

    Best wishes
    Michael
  • 4. Re: How to manage Discoverer Active Users?
    701988 Newbie
    Currently Being Moderated
    Hey Michael,

    Thanks for the explanation
    Your query was useful for finding the last run of users,
    I created a query for getting the users who haven’t created any reports after a particular date.

    SELECT EU_USERNAME FROM EUL5_EUL_USERS
    WHERE EU_ROLE_FLAG = 0
    AND EU_USERNAME NOT IN (SELECT QS_CREATED_BY FROM EUL5_QPP_STATS
    WHERE QS_CREATED_DATE > '01-JAN-2009')

    Do you think this will do the job?

    Visakh
  • 5. Re: How to manage Discoverer Active Users?
    Michael Armstrong-Smith Oracle ACE
    Currently Being Moderated
    Hi Visakh
    That will do the job nicely. That will tell you the names of the user(s) who have not had a workbook executed during the period. You may also want to specifically exclude the PUBLIC user and, depending upon your setup, the EUL owner too, like this, along with any other username that comes up who should never own workbooks:

    SELECT EU_USERNAME FROM EUL5_EUL_USERS
    WHERE EU_ROLE_FLAG = 0
    AND EU_USERNAME NOT IN ('PUBLIC','EULOWNER')
    AND EU_USERNAME NOT IN (SELECT QS_CREATED_BY FROM EUL5_QPP_STATS
    WHERE QS_CREATED_DATE > '01-JAN-2009');

    You could also use the DOC_UPDATED_DATE in the EUL5_DOCUMENTS folder to get a listing of all of the workbooks which are owned by those users. Tying these to the EUl5_ACCESS_PRIVS table on the GD_DOC_ID will let you know if the workbook has been shared.

    Let me know if this completes this question.

    Best wishes and good luck
    Michael
  • 6. Re: How to manage Discoverer Active Users?
    701988 Newbie
    Currently Being Moderated
    Hi,

    -- list of users whose account was created before 01-JAN-2009 and who haven't had a workbook executed

    SELECT EU_USERNAME FROM EUL5_EUL_USERS
    WHERE EU_ROLE_FLAG = 0
    AND EU_USERNAME NOT IN ('PUBLIC','EULOWNER')
    AND EU_USERNAME NOT IN (SELECT QS_CREATED_BY FROM EUL5_QPP_STATS)
    AND EU_CREATED_DATE < '01-JAN-09'

    another issue was that newly created users who haven't created reports should be preserved.
    anyway it's solved now EU_CREATED_DATE < '01-JAN-09'

    thanks
    visakh
  • 7. Re: How to manage Discoverer Active Users?
    701988 Newbie
    Currently Being Moderated
    Issue is solved
    Thanks to Michael

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points