How many users

From time to time I am asked to provide scripts that help administrators manage their Discoverer system. One such example is below.

I was asked if I knew of a way for a company to know how many users they had using Discoverer so that they can make sure they are compliant with their licensing. The following script will do just that, plus a little more:

SELECT DISTINCT
UPPER(QS.QS_DOC_OWNER) USERNAME,
COUNT(QS_ID)REPORTS_RUN,
MAX(QS.QS_CREATED_DATE) LAST_USED,
MIN(QS.QS_CREATED_DATE) FIRST_USED
FROM EUL5_QPP_STATS QS
WHERE QS.QS_CREATED_DATE > '01-JAN-2006' AND
QS_DOC_OWNER IS NOT NULL
GROUP BY UPPER(QS.QS_DOC_OWNER)
ORDER BY 1;

Note: there is a restriction to running this script in that it will only tell you about users who have their own workbooks and when they were last ran. If you are using Discoverer connections you will not be able to use these scripts because the workbook will not necessarily be owned by the user who owns the connection or, if using SSO, by the user who is logged in. I am still researching if there is a way to derive this information.

The date parameter is there to check users who have been on the system from that date. This way you can exclude reports that were run a long time ago. You can drop it altogether if you like. If you do this you will see the total number of users you have had running reports. Of course, this assumes that you are collecting query statistics and that you haven't purged them - which will be the subject of another posting.

The report will tell you who has run a report in Discoverer, how many reports the user has executed, when the user first logged in and when the user last logged in to run a report. Using this information you can determine how many licenses you need or just to see who are the most frequent Discoverer users on your system.

To accomplish the latter just add another clause. Let's say you only want to see users who have run at least 25 reports. All you need to do is this:

SELECT DISTINCT
UPPER(QS.QS_DOC_OWNER) USERNAME,
COUNT(QS_ID)REPORTS_RUN,
MAX(QS.QS_CREATED_DATE) LAST_USED,
MIN(QS.QS_CREATED_DATE) FIRST_USED
FROM EUL5_QPP_STATS QS
WHERE
QS.QS_CREATED_DATE > '01-JAN-2006' AND
QS.QS_DOC_OWNER IS NOT NULL
GROUP BY UPPER(QS.QS_DOC_OWNER)
HAVING COUNT(QS_ID) >= 25
ORDER BY 1;

Finally, let's say you want to extend the SQL again to include only users who have run a report within the last month yet still include users who have run at least 25 reports since 2006. This dynamic script will do that:

SELECT DISTINCT
UPPER(QS.QS_DOC_OWNER) USERNAME,
COUNT(QS_ID)REPORTS_RUN,
MAX(QS.QS_CREATED_DATE) LAST_USED,
MIN(QS.QS_CREATED_DATE) FIRST_USED
FROM EUL5_QPP_STATS QS
WHERE
QS.QS_CREATED_DATE > '01-JAN-2006' AND
QS_DOC_OWNER IS NOT NULL
GROUP BY UPPER(QS.QS_DOC_OWNER)
HAVING
COUNT(QS_ID) >= 25 AND
MAX(QS.QS_CREATED_DATE) >= TRUNC(SYSDATE,'MM')
ORDER BY 1;