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.
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.
-- 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'