Find inactive users

I'm using LL 8.1.5 and SQL7 and try to find all the inactive users who haven't used since 30 days.I try the following SQL, but it doesn't work. select a.firstname, a.lastname from kuaf a, daudit b where b.event = 'LOGIN' AND a.id = b.userid AND deleted = 0 AND ((max(b.auditdate)) < (sysdate -30))group by a.Firstname, a.LastnameHow can I improve it in order to get a list of inactive users? Thanks!

Find more posts tagged with

Comments

eLink User

Message from Sean M Alderman via eLinkYou may find that your date formats aren't comparable, and that's why your query isn't working. You might need to massage the formats with the to_char/to_date functions...but that's a guess.Also, users who have never logged in will not show up in query results based on the daudit.event = 'LOGIN' criteria...since they have never logged in... To find those users, you may want to try the query below. The only downfall is that there is now way to determine if a user returned by this has recently been created -select from kuaf where deleted = 0 and type = 0 and id not in (select userid from daudit where event = 'LOGIN')I'm almost sure there's something to limit the subquery's results to unique userid's, but I can't seem to figure out what it is.p.s. This won't solve your problem but you should probably add a to trim the group records from your query.At 03:56 PM 05/02/2001 -0400, you (eLink Discussion: Livelink LiveReports Discussion) wrote:>Find inactive users>Posted by xie on 05/02/2001 03:54 PM>>I'm using LL 8.1.5 and SQL7 and try to find all the inactive users who haven't used since 30 days.>I try the following SQL, but it doesn't work. >>select a.firstname, a.lastname from kuaf a, daudit b where b.event = 'LOGIN' >AND a.id = b.userid >AND deleted = 0 >AND ((max(b.auditdate)) < (sysdate -30))>group by a.Firstname, a.Lastname>>How can I improve it in order to get a list of inactive users?> >Thanks!>>>[To reply to this thread, use your normal e-mail reply function.]>>============================================================>>Discussion: Livelink LiveReports Discussion>https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view>>Livelink Server:>https://knowledge.opentext.com/knowledge/livelink.exe- Sean M. AldermanITRACK Systems AnalystPACE/NCI - NASA Glenn Research Center(216) 433-2795

Alex_Kowalenko_(akowalen_(Delete)_2285456)

Inactive Users Queryselect u.lastname || ', ' || u.firstname || ' (' || u.name || ')' as "Inactive User" from kuaf u, daudit a where u.id = a.userid (+) and u.type = 0 and u.deleted = 0 and a.userid is null order by 1Note that this is Oracle SQL.

Alex_Kowalenko_(akowalen_(Delete)_2285456)

This query lists users who have NEVER logged in.