users who haven't logged on in the last 2 years

Clare_Turner

does anyone have a report to list users who have not logged into the system recently? We are running version 9.7.1

Find more posts tagged with

Comments

Nicole_Bride

Message from Bride, Nicole <nbride@cvps.com> via eLink

eLink

You could try starting with this:

select distinct b.name, max(a.auditdate)

from dauditnew a, kuaf b

where a.performerid=b.id and a.AuditStr='Login' and b.name not like'%%(delete)%%'

group by b.name

order by max(a.auditdate) desc

From: eLink Discussion:Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, September 30, 2009 11:44 AM
To: eLink Recipient
Subject: users who haven't logged on in the last 2 years

users who haven't logged on in the last 2 years

Posted by clare.turner@manchester.ac.uk (Turner, Clare) on 2009/09/30 11:40

does anyone have a report to list users who have not logged into the system recently? We are running version 9.7.1

[To reply to this thread, use your normal E-mail reply function.]
Discussion:	Open Text Live Reports Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi Clare,

Based on Nicole's suggestion, you might try something like this (for an Oracle database)....

select name,performerid, max(auditdate) last_action from dauditnew,kuaf where kuaf.id=performerid group by performerid,name having max(auditdate) < sysdate-365 order by 1

This reports users who have not caused any auditing in the last 365 days.

A similar construct would work for SQL server, but you will need to check difference from the current date slightly differently.

select name,performerid, max(auditdate) last_action from dauditnew,kuaf where kuaf.id=performerid group by performerid,name having max(auditdate) < getdate() -365 order by 1

If you use the Directory Services module you might configure integrated authentication, so you would not get the login screen and then there would be no login event to check for.

Regards

Lindsay Davies

European Escalation Team

Open Text

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 30 September 2009 16:44
To: eLink Recipient
Subject: users who haven't logged on in the last 2 years

users who haven't logged on in the last 2 years

Posted by clare.turner@manchester.ac.uk (Turner, Clare) on 2009/09/30 11:40

does anyone have a report to list users who have not logged into the system recently? We are running version 9.7.1

Evert_Kuiken_(ekuiken_-_(deleted))

You could also avoid using any audit information and joins using the Password Expiry date (if you use an Expiry schedule). E.g. this:select * from kuaf where pwdexpiredate<sysdate-730 and deleted=0Include "and not groupid=1001" if you want to exclude users in the DefaultGroup group.Evert

Magda_Wurzl