Active Agile users who have not accessed the system
Hello All,
I have to achieve following two points:
1. A query which can return all active users and their actions taken in last 2 years.
2. The list of users who have not approved/modified or made a change to system/files.
For the first point, (please correct me if I am wrong) I have an understanding that the history tab of user class interface shows the activities so far performed by a user. Using this understanding I have developed the following query:
SELECT *
FROM (SELECT ih.id,
le.entryvalue AS action,
au.loginid AS “user”,
(SELECT DateDiff (
‘YYYY’,
(SELECT localClientTime
FROM ( SELECT user_id,
TIMESTAMP AS localClientTime
FROM ITEM_HISTORY
ORDER BY localClientTime ASC)
WHERE user_id = ih.user_id AND ROWNUM <= 1),
(SELECT localClientTime
FROM ( SELECT user_id,
TIMESTAMP AS localClientTime
FROM ITEM_HISTORY
ORDER BY localClientTime DESC)
WHERE user_id = ih.user_id AND ROWNUM <= 1))
AS yeardiff
FROM DUAL)
AS yeardiff,
ih.TIMESTAMP AS localClientTime,
ih.DETAILS AS details,
ih.comments
FROM ITEM_HISTORY ih
LEFT OUTER JOIN listentry le
ON le.entryid = ih.action AND le.parentid = 4456
LEFT OUTER JOIN agileuser au
ON au.id = ih.user_id
WHERE 1 = 1 –and user_id = 6607658
AND au.enabled = 1
and ih.class = 11605)
WHERE yeardiff >= 2
ORDER BY localClientTime ASC;
Another query which I have developed is as follows:
SELECT *
FROM (SELECT a.userid,
au.loginid,
(SELECT DateDiff (‘YYYY’,
(SELECT localClientTime
FROM ( SELECT userid,
TIMESTAMP AS localClientTime
FROM ADMIN_HISTORY
ORDER BY TIMESTAMP ASC)
WHERE userid = a.userid AND ROWNUM <= 1),
(SELECT localClientTime
FROM ( SELECT userid,
TIMESTAMP AS localClientTime
FROM ADMIN_HISTORY
ORDER BY TIMESTAMP DESC)
WHERE userid = a.userid AND ROWNUM <= 1))
AS yeardiff
FROM DUAL)
AS yeardiff,
a.timestamp,
ah.object,
ah.details
FROM ADMIN_HISTORY a
INNER JOIN admin_history_details ah
ON a.detailid = ah.id
INNER JOIN agileuser au
ON AU.ID = a.userid AND au.enabled = 1)
WHERE yeardiff >= 2
Where DateDiff is a UDF I have created to calculate years.
For the second point, I do not have any sort of understanding so far. Is there any way by which I can achieve both points using a single query?
I have to achieve both points using only SQL query.
The way I accomplished this is to get unique userIDs that popped up in the history over the past 6 months. Then compared that list to the active IDs and got the diff.
This is what I did. But I need an expert to verify the query.
SELECT distinct *
FROM (SELECT a.userid,
au.loginid,
(SELECT DateDiff (‘YYYY’,
(SELECT localClientTime
FROM ( SELECT userid,
TIMESTAMP AS localClientTime
FROM ADMIN_HISTORY
ORDER BY TIMESTAMP ASC)
WHERE userid = a.userid AND ROWNUM <= 1),
(SELECT localClientTime
FROM ( SELECT userid,
TIMESTAMP AS localClientTime
FROM ADMIN_HISTORY
ORDER BY TIMESTAMP DESC)
WHERE userid = a.userid AND ROWNUM <= 1))
AS yeardiff
FROM DUAL)
AS yeardiff,
–a.timestamp,
le.entryvalue,
ah.object,
dbms_lob.substr ( ah.details, 2000, 1 )AS details
FROM ADMIN_HISTORY a
INNER JOIN admin_history_details ah
ON a.detailid = ah.id
INNER JOIN agileuser au
ON AU.ID = a.userid AND au.enabled = 1
LEFT OUTER JOIN listentry le
on le.entryid = a.actionid
and le.parentid = 4506
)
WHERE 1=1
— and USERID = 6028735
AND yeardiff >= 2
You would be far better off creating a table to hold the data you need, and then writing a PL*SQL script to put the data in there. Along the way, it can check for users that haven’t done anything and either directly report them or enter a special record into the table specifically for users that haven’t done anything.
Start with the ID for all users that are active (select id, loginid, first_name, last_name from agileuser where enabled=1). For each ID value, look in ITEM_HISTORY, ADMIN_HISTORY and CHANGE_HISTORY for the desired time period – the past 2 years in your case (select count(*) from item_history where user_id = UID and timestamp > to_date(’07/11/2016′,’MM/DD/YYYY’) for example). If no records are found in any of those tables, that user hasn’t done anything, and your script can note this.
If records are found, put them into your table with correct text values. You can then get aggregate stuff from the table you just created (management *loves* statistics) or just dump it out into a spreadsheet. You have the list stuff already in your query, so you know how to do that. Also note that the reason I included the first/last name from AGILEUSER in my main query is that although ITEM/CHANGE_HISTORY both have the full user name values in the table(USER_NAME), ADMIN_HISTORY does not have such an attribute. So it is good to get that from AGILEUSER along with the ID.
And I am checking all 3 history tables because of you are a change analyst, you may have never modified a part/document field or modified the configuration, but you certainly *did* stuff. You might also look in USER_USAGE_HISTORY to see if they have even logged in to view stuff (select count(*) from user_usage_history where username = LOGINID).
