|
|
Archives of the TeradataForum
Message Posted: Wed, 19 Jun 2013 @ 13:50:29 GMT
Subj: | | Re: Query to find users not logged in since long days |
|
From: | | JAMES PARK |
Hi,
This may work.
sel a.*, b.*
from (sel username, substr(commentstring,1,30) "Full Name",
cast(createtimestamp as date format 'MM-DD-YYYY') CreateDate
from dbc.users
/* Select users that were created at least 60 days ago */
where
Username not in ('All_Users','SysAdmin','Sys_Calendar')
and cast(createtimestamp as date) <= current_date - 60 ) a left outer join (sel
username, max(Logondate) latest_logon, current_date - max(Logondate) days_since from
sys_hist.logonoff_hist where event='Logon'
and Username not in ('All_Users','SysAdmin','Sys_Calendar')
group by 1 ) b
on a.username=b.username
/* Select users that have not logged on in the last 45 days */ where days_since is
null
or days_since >= 60
order by 1;
James Park
Database Specialist
| |