Message from Bhupinder Singh <bsingh@opentext.com> via eLink
The following SQL query, when used in a LiveReport with report format set to "AutoReport", will give a list of all users who have ever logged into your Livelink system (ensure you clear the "record limit" field in the LiveReport edit page to get all records).
select distinct kuaf.firstname, kuaf.lastname from dauditnew, kuaf where dauditnew.performerid=kuaf.id and dauditnew.AuditStr='Login' order by kuaf.lastname
The next query below will show everyone who logged in, and when, starting from the most recent login:
select dauditnew.auditdate, kuaf.firstname, kuaf.lastname from dauditnew, kuaf where dauditnew.performerid=kuaf.id and dauditnew.AuditStr='Login' order by dauditnew.auditdate desc
You can customize the above query to find out who logged in most recently but introducing additional restrictions based on the date fields in the DAuditNew table. Please test the validity of the above queries yourself before trusting them to report on production systems.
This query shows the last time a user logged in (set Input to be a User-type, i.e. whoever runs the report will get to select the user in question, and set param %1 to be "User Input 1"). This query might need some optimization to improve run-time:
select KUAF.Name,KUAF.FirstName,KUAF.LastName,max(DAuditNew.AuditDate) "Last Login" from DAuditnew,KUAF where KUAF.ID=DAuditnew.Performerid and KUAF.ID=%1 and upper(DAuditnew.AuditStr)='LOGIN' and DAuditnew.AuditDate>'01-JAN-80' group by DAuditnew.performerID,KUAF.Name,KUAF.FirstName,KUAF.LastName
A variation of the above query which looks at users in a group can be found here:
https://knowledge.opentext.com/go/14756328
For all of the above queries to work, ensure that "Login" is configured to be an audited event.
1. Login to Livelink as the Livelink Admin user.
2. Access the Livelink Administration pages using the ?func=admin.index URL extension.
3. Scroll down to the "System Adminstration" section and click the "Administer Event Auditing" link
4. Click the "Set Auditing Interests" link
5. See if the checkbox for "Log-in" is checked. There is also a checkbox for "Log-out" if you wish to track that event as well.
Let me know if those help you get started on building queries specific to your needs.
- Bhupinder
---------------------------------------------- Bhupinder Singh, B.Math, B.Ed. Senior Systems Analyst, Information Technology Open Text, Waterloo, Ontario, Canada ----------------------------------------------
You know this is a pretty frequentrequest. It seems to me that if we set a bit on when a user logs in and set itto off when they log out, the report would be easy to produce. Not sure howlapi calls would be affected by that but it just seems like knowing who iscurrently logged in should be an easy report to create if we had one bit thatwould track it for us. Or am I way off base on this?
--
Greg Kellogg
(361) 815-8950
From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, November 28, 20083:39 PMTo: eLink RecipientSubject: RE Hi Bhupinder,
RE Hi Bhupinder,
Posted by bsingh (Singh, Bhupinder) on 11/28/2008 04:38 PM
In reply to: Hi Bhupinder,
Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 11/28/2008 12:04 PM
1. Login to Livelink as the Livelink Admin user
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, November 28, 2008 12:09 PMTo: eLink RecipientSubject: Hi Bhupinder,
Hi Bhupinder,
In reply to: RE Current online users
Posted by bsingh (Singh, Bhupinder) on 11/28/2008 11:43 AM
Hi Bhupinder, Thanks for the reply. We have single sign on enabled. So users are not shown login screen. But I have checked that everytime they open Livelink in the Internet Explorer, open the Livelink Explorer, access Livelink node in Outlook, it is counted as a login. So on that basis if a user is using Livelink from either of these 3 means, then is it possible to pull out these data from SQL database? Many Thanks, Atiya.
Thank you Bhupinder, this is veryhelpful. I modified it slightly to list all users, and not show deleted ones:
select distinct b.name, max(a.auditdate)
from dauditnew a, kuaf b
where a.performerid=b.id anda.AuditStr='Login' and b.name not like '%%(delete)%%'
group by b.name
order by max(a.auditdate) desc
What is very cool is that I can justchange ‘Login’ to another event to get some usage statistics (howmany people are actually adding documents).
Thanks again!
From: eLinkDiscussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, November 28, 20084:39 PMTo: eLink RecipientSubject: RE Hi Bhupinder,
Message from Bride, Nicole <nbride@cvps.com> via eLink
You could write a vbscript, and run it against the server. There is a sample one here (under the heading "Determing Process Ownership").
