Needed livereport reg users login info

Livelink version 9.7.1 and database Sql server 20051. List of users who never logged in past 3 months2. List of users who never logged in (Inactive users)Needed these reports urgently. Thanks in advance.

Find more posts tagged with

Comments

Appu_Nair

Please use the search functionality of this discussion forum,I am sure you will find at least a dozen examples of what you are after and let the forum know where you are getting stuck.Post whatever you have done so far.

Sowmini_Jayapal

Please let me know where i went wrong in below queries.1.List of users who have not logged in past 3 months.select a.firstname + ' ' + a.lastname "User Name", a.name "Login Name", b.name "Department" from kuaf a, kuaf bwhere not exists (select c.performerid from dauditnew c where (c.performerid = a.id) and (c.auditstr = 'LOGIN') and (c.auditdate < (getdate()-90))) and a.groupid = b.ID anda.deleted <> 1 and a.type = 0 order by a.lastname2. List of users who never logged inselect a.name "Login Id", a.firstname + ' ' + a.Firstname "Name" from kuaf a RIGHT OUTER JOIN dauditnew b ON a.id = b.performerid and a.type = 0 and a.deleted = 0 and b.performerid is null order by a.lastname

Appu_Nair

Based on this thread by Eric Saavedra your first query can be simplified like thishttps://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=12475615&objAction=view/* USERS WHO HAVE NOT LOGGED IN FOR THE PAST 190 DAYS */SELECT FIRSTNAME, LASTNAME, NAMEFROM KUAF WHERE TYPE = 0 AND DELETED = 0 AND ID NOT IN(SELECT PERFORMERID FROM DAUDITNEW WHERE AUDITSTR = 'Login' AND AUDITDATE > GETDATE() – 90)I cannot say I understand your logic on that first queryI will keep looking for stuff about people never having logged in,but in my mind dauditnew records login if that entry is audited.So if you have a livelink database of 100 users with 75 logging in and 25 not logging in.select * from kuaf where deleted=0 and type=0 will give you the 100 usersthen do a distinct records of userid for your time period on dauditnew based on the login string like the first query,this will give you the 75 usersAt this point I should be able to devise my query

Tim_Hunter

This will give you users who have never logged inSELECT k.name, k.idFROM kuaf kLEFT OUTER JOIN dauditnew aON a.performerid =k.idAND a.AuditStr ='Login'WHERE a.eventid IS NULLAND k.deleted = 0AND k.type =0