Message from Anne Callanan via eLinkDear AlexI thought I acknowledged the reports I had got from your entries on the knowledge base. This is just to say I do have acopy of the Livelink Schema Reference.Thanks very muchAnneeLink Discussion: Livelink LiveReports Discussion wrote:> RE Re Re Re Report on User Count, find inactive, Groups etc.> Posted by eLink on 05/02/2001 02:06 PM>> Message from Alex Kowalenko via eLink>> Earlier in this thread Ann Callanan posted one of my LiveReports from the> discussion that purports to find all groups for a user. This LiveReport uses> the KUAFRightsList table to lists all groups for a user. The problem with> the KUAFRightsList is that it is created and updated from KUAFChildren only> when users log in. So, if a user has never logged in then there will be no> results - or out of date results for infrequent users.>> I rewrote this LiveReport (attached) to do a hierarchical query on> KUAFChildren to "walk" the tree and find all groups in groups in groups,> etc. Note that this LiveReport is in Oracle lingo and must be translated> using a resource of your choice to appropriate MS SQL and Sybase.>> As Sean indicated, a copy of the Livelink System Schema Reference is very> helpful for complex LiveReport creation. Information on how to get this> document is available through your Open Text account representative.>> --> Alex Kowalenko> Open Text Professional Services>> -----Original Message-----> From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf Of> eLink Discussion: Livelink LiveReports Discussion> Sent: Wednesday, May 02, 2001 12:49> To: eLink Recipient> Subject: 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 PM>> Message from Sean M Alderman via eLink>> If 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 b> where b.event = 'LOGIN' AND a.id = b.userid AND deleted = 0> group 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.name> from kuaf a, kuaf b> where 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. Alderman> ITRACK Systems Analyst> PACE/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=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>> [To reply to this thread, use your normal e-mail reply function.]>> ============================================================>> Attachment link: All Groups LiveReport.txt>
https://knowledge.opentext.com/knowledge/livelink.exe/2514219/All_Groups_LiveReport.txt?func=doc.Fetch&nodeid=2514219>> ============================================================>> 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>> ------------------------------------------------------------------------> Name: All Groups LiveReport.txt> All Groups LiveReport.txt Type: Plain Text (text/plain)> Encoding: 7BIT--Anne Callanan - a.callanan@unl.ac.ukIntranet Services TeamUniversity of North LondonTelephone: 020 7607 2789 Extension: 2389
