nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Need SQL query for users not logged in for the last year

Jitesh_Amin

We need to purge users that haven't accessed the instance in the last (period of time), figured it would be similar to:SELECT Name FROM KUAF WHERE ID NOT IN (SELECT UserID FROM DAudit WHERE Event='LOGIN') and type = 0and deleted = 0with additional logic in the select, any ideas?

Find more posts tagged with

Comments

Joe_Vicioni

Message from Joe Vocioni <Joe.Vocioni@gov.ab.ca> via eLink

eLink

Hey Brad,

Here’s something we use to see whohasn’t logged in the last 30 days. The query is for the 9.7 schema but acouple modifications and it should do what you need it to do.

/* list users who have not logged in tothe system within the last 30 days */

select k.firstname || ' ' || k.lastname"User Name",

k.name "Login Name",

max(d.auditdate) "Last LoginDate"

from kuaf k,

dauditnew d

where d.performerid = k.id

and not exists (select performerid

from dauditnew

where performerid = k.id

and auditid = 23

and (auditdate >=(sysdate-30)))

and k.deleted <> 1

and k.type = 0

group by k.lastname, k.firstname, k.nameorder by k.lastname

From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, April 29, 2008 7:58AM
To: eLink Recipient
Subject: Need SQL query for usersnot logged in for the last year

Need SQL query for users not logged in for the last year

Posted by locgmd01admin (Birth, Brad) on 04/29/2008 09:56 AM

We need to purge users that haven't accessed the instance in the last (period of time), figured it would be similar to:

SELECT Name
FROM KUAF
WHERE ID NOT IN (SELECT UserID FROM DAudit WHERE Event='LOGIN')
and type = 0
and deleted = 0

with additional logic in the select, any ideas?

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

Matthew_Barben_(matthew.barben@logica.com_(Delete)

I have used the following query which lists the person's department and Last Login DateSELECT K1.ID "User ID", K1.FIRSTNAME "First Name", K1.LASTNAME "Last Name", K1.NAME "User Name", K2.NAME "Dept Name" ,(SELECT TOP 1 DAUDITNEW.AuditDate FROM DAUDITNEW where DAUDITNEW.PERFORMERID = K1.ID AND DAUDITNEW.AuditStr = 'Login' ORDER By AuditDate DESC) "Last Login Date"FROM KUAF K1, KUAF K2WHERE K1.TYPE = 0 AND K1.DELETED = 0 AND K1.NAME Like '%-ex' AND K2.ID = K1.GROUPID AND K1.DELETED = 0AND K1.ID NOT IN (SELECT PERFORMERID FROM DAUDITNEW WHERE AUDITSTR = 'Login' AND AUDITDATE > GETDATE()-365)