Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 19 Jun 2013 @ 13:50:29 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Query to find users not logged in since long days


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 */
      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
       or days_since >= 60
     order by 1;

James Park
Database Specialist

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 24 Jul 2020