LiveReport to show who users login last month?

Tim_Hoang_(ASERA01Admin_(Delete)_2490215)

Is there anyway to generate a LiveReport to show who login to the system last month?Appreciate any help--Michael C

Find more posts tagged with

Comments

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Yes. Here is a basic SELECT statement that will list all the users who logged in since a specified date (with Auto LiveReport format):

select name, auditdate from daudit, kuaf where auditdate >= %1 and event="LOGIN" and kuaf.id = daudit.userid

%1 can be mapped to "Last month-start" or "This month-start" or a user input of a start date.

-----Original Message-----
From: knowledge@opentext.com [mailto:knowledge@opentext.com]On Behalf Of eLink Discussion: Livelink LiveReports Discussion
Sent: Thursday, April 04, 2002 7:25 PM
To: eLink Recipient
Subject: LiveReport to show who users login last month?

LiveReport to show who users login last month? Posted by ASERA01Admin (Hoang, Tim) on 04/04/2002 08:20 PM

Is there anyway to generate a LiveReport to show who login to the system last month?

Appreciate any help

--Michael C

Vera_Verbist_(dvvadmin_-_(deleted))

A better report is this one:select count(distinct(userid)) "Aantal", trunc(auditdate) "Datum" from daudit where event = 'LOGIN' and (auditdate >= %1 and auditdate <=%2) group by trunc(auditdate)%1 is start month and %2 is end month Vera Verbist

Tim_Hoang_(ASERA01Admin_(Delete)_2490215)

Vera,I tried your script and here the error I got:[ORA-00911: invalid character - select count(distinct(userid)) "Aantal", trunc(auditdate) "Datum" from daudit where event = 'LOGIN' and (auditdate >= ? and auditdate <=?) group by trunc(auditdate)] Please adviceAppreciate your help--Michael C

Vera_Verbist_(dvvadmin_-_(deleted))

Hi Michael,Hereby I send you the exported report. I hope this will work when you imported it. Here I don't have any problems with it

James Mernin

I tried to run the Report in SQL as suggested previously and recieved an error on the "trunc" command not being recognized by MS SQL. So I removed that statement, set %1 and %2 to User Prompts and promted for the dates.Attached is my result set.

Vera_Verbist_(dvvadmin_-_(deleted))

Hi Michael,Yes indeed, we're working with Oracle, that's why it didn't work on your machine. The trunc was to have just 1 date per user.Kind regards,Vera

Tim_Hoang_(ASERA01Admin_(Delete)_2490215)

Thanks Erik,I got this report works and also the follow one is good too:select Count(daudit.USERID) "Logins", KUAF.LastName || ', ' || KUAF.FirstName "Name", KUAF.Name "ID", MAX(AuditDate) "Last Access" From KUAF, DAudit WHERE KUAF.Type = '0' and KUAF.Deleted = '0' and daudit.AUDITDATE>Sysdate-30 and KUAF.ID = DAudit.UserID (+) Group by LastName, FirstName, Name order by max(AuditDate)BTW: Do you know how- if I want to report the no. of User login each day for a time frame of a month in pie chart format?Thanks again, appreciate for your help.

Jay_Harbeston

In your Livereport:select Count(daudit.USERID) "Logins", KUAF.LastName || ', ' || KUAF.FirstName "Name", KUAF.Name "ID", MAX(AuditDate) "Last Access" From KUAF, DAudit WHERE KUAF.Type = '0' and KUAF.Deleted = '0' and daudit.AUDITDATE>Sysdate-30 and KUAF.ID = DAudit.UserID (+) Group by LastName, FirstName, Name order by max(AuditDate)what is "...Count(daudit.USERID) "Logins"..." collecting? Is it the number of times the individual user has logged-in for the month, or what? I thought it was, until I verified the number(s) against an Admin Audit Query for the individual users, and found most of the "Login" numbers didn't agree with the LiveReport.I really like your report, thanks...Mike HigginsLL Admin - GDAIS

Robert_Davies_(unlondonadmin_-_(deleted))

To limit the query to logins only you need'where Daudit.Event = 'LOGIN' as well as your other selection criteriaBest RegardsAnne