How to identify inactive users?

[pjb]

I use the following sql to identify users who have not accessed livelink this year:

select a.name, a.mailaddress, max(b.auditdate) from kuaf a, daudit b
where b.event = 'LOGIN' AND a.id = b.userid AND deleted = 0 AND b.auditdate < '2005-01-01'
group by a.name, a.mailaddress;

Is this use of auditdate good for what I need?

 

[appnair]

Depending on how much auditdates are in daudit your query will return anything below your date if the date format is right.Not exactly...I spot a problem,if it is oracle.Livelink stores time information upto the second so you may need to do a trunc on your date filled for proper comparison

Give me a count of logins between today's date and
the date last year,By the way for livelink version lesser than 9.5

select a.name, a.mailaddress, max(b.auditdate),count(b.event) from kuaf a, daudit b 
where b.event = 'LOGIN' AND a.id = b.userid AND deleted = 0 AND trunc(b.auditdate)> trunc(sysdate-365 ) and 
trunc(b.auditdate)< trunc(sysdate ) group by a.name, a.mailaddress

Try this and see if you can make it work.All I am doing is saying give me between now and 365 days prior.The count should give the number of logins a person made.If you really where particularly nosy,you can have an active user
in livelink without privs for login.If you want to trap that you can do some bitwise stuff .Let me know if you want that.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937