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; } } }

Report on User Count, find inactive, Groups etc.

Susan_Wood_(genuclearadmin_-_(deleted))

We are using LL 8.1.5 and SQL71. Is there a good resource for shared Live Reports? We assume reports we need to create would also be usefull to others?2. We need to report on the following information:a) User Countb) Inactive Users based on last logon date or interval such as one month, three month etc.c) User lastname, firstname, Login name, title, Contact, Base Groupd) All Groups each user belongs to

Find more posts tagged with

Comments

Robert_Davies_(unlondonadmin_-_(deleted))

Dear SusanPlease find attached a text file with SQL that should help you address your queries. Although it is Oracle SQL I have given a useful Web Site that gives pointers on converting from Oracle SQL to Microsoft SQL. I have also given an explanation of any symbol I use that I know is not MS SQL so you should be able to substitute the equivalent symbol fairly easily.Let me know if this is of any use.Best regardsAnne Callanan

eLink User

Message from Sean M Alderman via eLinkI have a few of your reports... I wrote them myself, and I'm not SQL expert, so there may be more efficient ways to accomplish the same tasks. If you'd like my sql let me know.At 12:07 AM 05/02/2001 -0400, you (eLink Discussion: Livelink LiveReports Discussion) wrote:>Report on User Count, find inactive, Groups etc.>Posted by GENuclearAdmin on 05/01/2001 06:19 PM>>We are using LL 8.1.5 and SQL7>1. Is there a good resource for shared Live Reports? We assume reports we need to create would also be usefull to others?>2. We need to report on the following information:>a) User Count>b) Inactive Users based on last logon date or interval such as one month, three month etc.>c) User lastname, firstname, Login name, title, Contact, Base Group>d) All Groups each user belongs to>>>[To reply to this thread, use your normal e-mail reply function.]>>============================================================>>Discussion: Livelink LiveReports Discussion>https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view>>Livelink Server:>https://knowledge.opentext.com/knowledge/livelink.exe- Sean M. AldermanITRACK Systems AnalystPACE/NCI - NASA Glenn Research Center(216) 433-2795

Robert_Davies_(unlondonadmin_-_(deleted))

Dear SeanI would be delighted to see your SQL solutions to these very common information problems.Anne Callanana.callanan@unl.ac.uk

eLink User

Message from Sean M Alderman via eLinkIf you're interested in writing live reports you should probably look into getting a schema reference from OpenText. Aside from that the only difficult query you mentioned was the last one. Depending on how your group structure is it can be difficult to list all groups a user belongs to...since groups can belong to groups, and so on.User count is simple -select count(*) from kuaf where type = 0 and deleted = 0;This will return a number count of your undeleted user accounts.I don't have an inactive accounts as you describe. I will give you the query I have to obtain the last login dates, note that a user who has never logged in will not show up in the results.select a.name, a.mailaddress, max(b.auditdate) from kuaf a, daudit bwhere b.event = 'LOGIN' AND a.id = b.userid AND deleted = 0group by a.name, a.mailaddress;This produces the username, email addy and last login date for all users who have logged in, since the last time you purged the dAudit table.The Lastname, firstname etc one is below -select a.lastname, a.firstname, a.name, a.title, a.contact, b.namefrom kuaf a, kuaf bwhere a.deleted = 0, a.type = 0, and a.groupid = b.id;Hope this helps, remember modify these as you need. Use the describe command to learn about the structure of a table.At 09:51 AM 05/02/2001 -0400, you (eLink Discussion: Livelink LiveReports Discussion) wrote:>Re Re Report on User Count, find inactive, Groups etc.>Posted by UNLondonAdmin on 05/02/2001 09:47 AM>>Dear Sean>>I would be delighted to see your SQL solutions to these very common information problems.>>Anne Callanan>>a.callanan@unl.ac.uk>>[To reply to this thread, use your normal e-mail reply function.]>>============================================================>>Topic: Report on User Count, find inactive, Groups etc.>https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2512785&objAction=view>>Discussion: Livelink LiveReports Discussion>https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view>>Livelink Server:>https://knowledge.opentext.com/knowledge/livelink.exe- Sean M. AldermanITRACK Systems AnalystPACE/NCI - NASA Glenn Research Center(216) 433-2795

eLink User

Message from Alex Kowalenko via eLinkEarlier in this thread Ann Callanan posted one of my LiveReports from thediscussion that purports to find all groups for a user. This LiveReport usesthe KUAFRightsList table to lists all groups for a user. The problem withthe KUAFRightsList is that it is created and updated from KUAFChildren onlywhen users log in. So, if a user has never logged in then there will be noresults - or out of date results for infrequent users.I rewrote this LiveReport (attached) to do a hierarchical query onKUAFChildren to "walk" the tree and find all groups in groups in groups,etc. Note that this LiveReport is in Oracle lingo and must be translatedusing a resource of your choice to appropriate MS SQL and Sybase.As Sean indicated, a copy of the Livelink System Schema Reference is veryhelpful for complex LiveReport creation. Information on how to get thisdocument is available through your Open Text account representative.--Alex KowalenkoOpen Text Professional Services-----Original Message-----From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf OfeLink Discussion: Livelink LiveReports DiscussionSent: Wednesday, May 02, 2001 12:49To: eLink RecipientSubject: Re Re Re Report on User Count, find inactive, Groups etc.Re Re Re Report on User Count, find inactive, Groups etc.Posted by eLink on 05/02/2001 12:49 PMMessage from Sean M Alderman via eLinkIf you're interested in writing live reports you should probably look intogetting a schema reference from OpenText. Aside from that the onlydifficult query you mentioned was the last one. Depending on how your groupstructure is it can be difficult to list all groups a user belongsto...since groups can belong to groups, and so on.User count is simple -select count(*) from kuaf where type = 0 and deleted = 0;This will return a number count of your undeleted user accounts.I don't have an inactive accounts as you describe. I will give you thequery I have to obtain the last login dates, note that a user who has neverlogged in will not show up in the results.select a.name, a.mailaddress, max(b.auditdate) from kuaf a, daudit bwhere b.event = 'LOGIN' AND a.id = b.userid AND deleted = 0group by a.name, a.mailaddress;This produces the username, email addy and last login date for all users whohave logged in, since the last time you purged the dAudit table.The Lastname, firstname etc one is below -select a.lastname, a.firstname, a.name, a.title, a.contact, b.namefrom kuaf a, kuaf bwhere a.deleted = 0, a.type = 0, and a.groupid = b.id;Hope this helps, remember modify these as you need. Use the describecommand to learn about the structure of a table.At 09:51 AM 05/02/2001 -0400, you (eLink Discussion: Livelink LiveReportsDiscussion) wrote:>Re Re Report on User Count, find inactive, Groups etc.>Posted by UNLondonAdmin on 05/02/2001 09:47 AM>>Dear Sean>>I would be delighted to see your SQL solutions to these very commoninformation problems.>>Anne Callanan>>a.callanan@unl.ac.uk>>[To reply to this thread, use your normal e-mail reply function.]>>============================================================>>Topic: Report on User Count, find inactive, Groups etc.>https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2512785&objAction=view>>Discussion: Livelink LiveReports Discussion>https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=view>>Livelink Server:>https://knowledge.opentext.com/knowledge/livelink.exe- Sean M. AldermanITRACK Systems AnalystPACE/NCI - NASA Glenn Research Center(216) 433-2795[To reply to this thread, use your normal e-mail reply function.]============================================================Topic: Report on User Count, find inactive, Groups etc.https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2512785&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

eLink User

Message from Alex Kowalenko via eLinkLiveReports can be shared in the LiveReport Submissions area at:https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2073344&objAction=browse&sort=nameWorthy submissions may be sent to Dave Slimmon (dslimmon@opentext.com).-alex------Original Message-----From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf OfeLink Discussion: Livelink LiveReports DiscussionSent: Wednesday, May 02, 2001 00:08To: eLink RecipientSubject: Report on User Count, find inactive, Groups etc.Report on User Count, find inactive, Groups etc.Posted by GENuclearAdmin on 05/01/2001 06:19 PMWe are using LL 8.1.5 and SQL71. Is there a good resource for shared Live Reports? We assume reports weneed to create would also be usefull to others?2. We need to report on the following information:a) User Countb) Inactive Users based on last logon date or interval such as one month,three month etc.c) User lastname, firstname, Login name, title, Contact, Base Groupd) All Groups each user belongs to[To reply to this thread, use your normal e-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

eLink User

Message from David Slimmon via eLinkYes indeed they can folks!Thanks Alex - everyone out there - please feel free to continue to send meyour sample LiveReports for publication on the KC. The discussion is great,but it's nice to have some good generic ones posted up there as text filesit seems.Cheers,Dave___________________________________________O P E N T E X T C O R P O R A T I O NDavid Slimmon, M.L.I.S.Supervisor - Customer SupportOttawa, Canadahttps://knowledge.opentext.comdslimmon@opentext.com-----Original Message-----LiveReports can be shared in the LiveReport Submissions area at:https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2073344&objAction=browse&sort=nameWorthy submissions may be sent to Dave Slimmon (dslimmon@opentext.com).-alex-