query for created users that habvent logged in in X amount of time

We are looking for the following query. A list of users that were created in last 3 months that havent logged in. Is this able to be put in a query? Any assitance appreciated.

Find more posts tagged with

Comments

Srini_Venkataramani

Message from Venkataramani, Srini <srini.venkataramani@ameristar.com> via eLink

Here's the SQL:

This count will EXCLUDE the users that are placed in the Deleted usersgroup and the users that are Disabled. If you want to include Deleted or the Disabledusers in the count, then comment out those respective AND clauses.

select a.firstname+' '+a.lastname as 'Name', a.mailaddress as 'Email Address',

max(b.auditdate) as 'Last Login Date'

from livelink.kuaf a, livelink.daudit b

where b.event = 'LOGIN'

AND a.id = b.userid

AND deleted = 0

AND GroupID <> 14793243 /* This is the Deleted Users Group.Replace this with the GroupID of the Deleted Users group for that LivelinkInstance */

AND UserPrivileges <> 14 /* This excludes Disabled Users */

group by a.firstname, a.lastname, a.mailaddress

HAVING max(b.auditdate) < %1

order by a.lastname asc

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, August 21, 2006 11:51 AM
To: eLink Recipient
Subject: query for created users that habvent logged in in X amount of time

query for created users that habvent logged in in X amount of time

Posted by Developers, Pfizer on 08/21/2006 02:47 PM

We are looking for the following query. A list of users that werecreated in last 3 months that havent logged in. Is this able to be put ina query? Any assitance appreciated.

[To reply to this thread, use your normal E-mail reply function.]

============================================================

Discussion: Livelink LiveReports Discussion

https://knowledge.opentext.com/knowledge/llisapi.dll/open/2249677

Livelink Server:

https://knowledge.opentext.com/knowledge/llisapi.dll

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

Hans Stoop

Message from Hans Stoop via eLinkI don't have the SQL for you, but I can tell you the concept:You need to have auditing for login enabled and you need to keep it for3 months.For the creation data of a user you can look at the creation date of itspersonal workspace. The object Idof the PWS is the same as the user ID.Hans > -----Original Message-----> From: eLink Discussion: Livelink LiveReports Discussion > [mailto:livereportsdiscussion@elinkkc.opentext.com] > Sent: maandag 21 augustus 2006 20:51> To: eLink Recipient> Subject: query for created users that habvent logged in in X > amount of time> > query for created users that habvent logged in in X amount of > time Posted by Developers, Pfizer on 08/21/2006 02:47 PM> > We are looking for the following query. A list of users that > were created in last 3 months that havent logged in. Is this > able to be put in a query? Any assitance appreciated.> > > > [To reply to this thread, use your normal E-mail reply function.]> > ============================================================> > Discussion: Livelink LiveReports Discussion> https://knowledge.opentext.com/knowledge/llisapi.dll/open/2249677> > Livelink Server:> https://knowledge.opentext.com/knowledge/llisapi.dll> > To Unsubscribe from this Discussion, send an e-mail to > unsubscribe.livereportsdiscussion@elinkkc.opentext.com.> > >

Steve_McDonough

This doesn't exactly respond to your needs - it doesn't filter the results for users created within the past 90 days - but it does use the DAuditNew table (for Livelink 9.2 and above). Perhaps it will give you a good basis for developing the report further:select kuaf.firstname + ' ' + kuaf.lastname "Member Name", kuaf.name "Login Name" from kuaf where not exists (select performerid from dauditnew where (performerid = kuaf.id) and (auditid = 23) and (auditdate <= (getdate()-90))) and kuaf.deleted <> 1 and kuaf.type = 0 order by kuaf.lastnameHope it helps!

Srini_Venkataramani

Message from Venkataramani, Srini via eLinkI forgot to mention that the SQL that I pasted before should be includedin a Livereport. The livereport prompts the user to select a date andthen shows all users that have been inactive since that date. To make the Livereport work, do the following:1) Create a new Livereport by going to Add Item - Livereport.2) Paste the SQL in the previous posting to the 'SQL' section of the LRscreen.3) Under the 'Inputs' section, declare an Input called 'Login Date' (youcan call it anything) of the type 'Date'. 4) Under the Param%1 field, select 'User Input 1'.5) For 'Report Format', select 'Auto Livereport'6) Leave all other fields as is.7) Click on Save and Execute. Enter the date when prompted. This shoulddisplay the list that you are looking for.Don't hesitate to give a shout if you have any problems with it.Srini-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, August 21, 2006 12:23 PMTo: eLink RecipientSubject: RE query for created users that habvent logged in in X amountof time

Dave_Ebels_(jocoadmin_-_(deleted))

I use the following report for a similar purpose. The first returns a report containing the user name and some other useful info , including the date & time of the last logon.Run as an Auto Live report with an input of a number, representing not having logged in for that number of days.Title : Not logged in in past <%1> of daysRecord Limit: (I have set 2000, this should be high enough to accomodate your needs)inputs: Number (prompt) Enter number of days not logged inSQL:select users.name,users.FIRSTNAME,users.LASTNAME, audt.auditdate from livelink.kuaf users, livelink.kuafchildren link,livelink.kuaf groups, (select userid,max(auditdate) auditdate from livelink.daudit where event in ('LOGIN') group by userid) audt where users.id = link.CHILDID and groups.id = link.ID and users.id = audt.userid and users.type = 0 and groups.type = 1 and audt.auditdate < sysdate -%1Param%1: User Input 1Auto livereportThe report above is quite useful for users not having logged in for a period of time.I have other reports that are variations on the theme if you would like to contact me. Dave Ebels , Johnson Controls , Dave.j.Ebels@jci.com

Chris_Jones

Message from Jones, Christopher L. \(IT\) via eLinkTo get the date a user account was created with 9.2 you will have to seewhen the personal workspace folder was created. -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, August 21, 2006 1:51 PMTo: eLink RecipientSubject: query for created users that habvent logged in in X amount oftimequery for created users that habvent logged in in X amount of timePosted by Developers, Pfizer on 08/21/2006 02:47 PMWe are looking for the following query. A list of users that werecreated in last 3 months that havent logged in. Is this able to be putin a query? Any assitance appreciated.[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/llisapi.dll/open/2249677Livelink Server:https://knowledge.opentext.com/knowledge/llisapi.dllTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.