7 Replies Latest reply: May 27, 2009 10:47 AM by 701988 RSS

    How to manage Discoverer Active Users?

    701988
      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
          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
            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
              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
                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
                  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
                    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
                      Issue is solved
                      Thanks to Michael