Wednesday, March 26, 2014

Find accounts unused for days

Find accounts unused for days
                                                

Summary
Here's a script that shows accounts and number of days since last use. It assumes session auditing is enabled.
SELECT RPAD(da.username, 12) "Username", 
TRUNC(SYSDATE - MAX(da.TIMESTAMP)) "Days Inactive",
LPAD(du.account_status, 16) "Status",
LPAD(TO_CHAR(MAX(da.TIMESTAMP), 'DD-MON-YYYY'), 16) "Last Login"
FROM dba_users du, dba_audit_session da WHERE da.action_name LIKE 'LOG%'
-- AND da.username NOT IN ('SYS','SYSTEM') -- itemize accounts to exclude
-- AND du.profile != '' -- or profiles to exclude
AND du.username = da.username
-- AND du.account_status = 'OPEN' -- could look at just OPEN if desired
GROUP BY da.username, du.account_status
--HAVING MAX(da.TIMESTAMP) <= SYSDATE - 1 ORDER BY 2,1 DESC;
 
The alternative if you don't want to enable auditing is to create a login trigger that stores login information in some table
But....
Logon triggers always have a little risk; if something goes wrong (and things can go wrong) logins could get blocked; for example, if the logon table is unavailable, or a record is locked etc. You must be at the edge to disable them if something go wrong.

No comments:

Post a Comment