Home
Extended ECM
API, SDK, REST and Web Services
User Last Login Date Report
Phillip_Mostert
Hi all,I need some help with a Live Report that will give me the following data:-User Firstname-User Surname-Department/Group-User Title-Date since last logged inI have a report currently running however it does not give me the Department/Group. This is what it looks like:SELECT KUAF.Name, KUAF.FirstName, KUAF.LastName, MAX(DAuditNew.AuditDate) "Last Login"FROM DAuditnew,KUAF WHERE KUAF.ID = DAuditnew.Performerid AND upper(DAuditnew.AuditStr)='LOGIN' and KUAF.Deleted = '0' AND DAuditnew.AuditDate >'01-JAN-80'GROUP BY DAuditnew.performerID, KUAF.Name, KUAF.FirstName, KUAF.LastNameThe statement is run against an Oracle database. Any help would be appreciated.
Find more posts tagged with
Comments
Evert_Kuiken_(ekuiken_-_(deleted))
Message from <
Evert.Kuiken@alliander.com
> via eLink
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink
What you need is an extra JOIN with the KAUF table in which you link the persons GROUPID th the ID of the group in order to get it's name. This is how I did it. Maybe it can be done more efficiently, I don't know, I am not an expert in writing SQL.
SELECT
k
.NAME,
k
.
firstname
,
k
.
lastname
,
g
.name,
MAX
(
dauditnew
.
auditdate
)
"Last Login"
FROM
dauditnew
,
kuaf k
,
kuaf g
WHERE
k
.ID
=
dauditnew
.
performerid
AND
UPPER
(
dauditnew
.
auditstr
)
=
'LOGIN'
AND
k
.
deleted
=
'0'
AND
dauditnew
.
auditdate
>
'01-JAN-80'
AND
k
.
GROUPID
=
g
.idGROUP
BY
dauditnew
.
performerid
,
k
.NAME,
k
.
firstname
,
k
.
lastname
,
g
.name
Regards, Evert
Van:
eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Verzonden:
dinsdag 24 november 2009 7:17
Aan:
eLink Recipient
Onderwerp:
User Last Login Date Report
User Last Login Date Report
Posted by
pmostert@datacentrix.co.za
(Mostert, Phillip) on 2009/11/24 01:12
Hi all,
I need some help with a Live Report that will give me the following data:
-User Firstname
-User Surname
-Department/Group
-User Title
-Date since last logged in
I have a report currently running however it does not give me the Department/Group. This is what it looks like:
SELECT KUAF.Name, KUAF.FirstName, KUAF.LastName, MAX(DAuditNew.AuditDate) "Last Login"FROM DAuditnew,KUAF WHERE KUAF.ID = DAuditnew.Performerid AND upper(DAuditnew.AuditStr)='LOGIN' and KUAF.Deleted = '0' AND DAuditnew.AuditDate >'01-JAN-80'GROUP BY DAuditnew.performerID, KUAF.Name, KUAF.FirstName, KUAF.LastName
The statement is run against an Oracle database. Any help would be appreciated.
Phillip_Mostert
Hi Evert, I combined my statement with yours and it works perfectly.Thanks,Phillip
Mary_Slane_Corona
PhillipWould you mind sharing your report?Thank you.
Phillip_Mostert
No problem, here it is:SELECT k.FirstName"Firstname", k.LastName"Lastname",k.title"Title", g.name"Department", MAX(DAuditNew.AuditDate) "Last Login"FROM DAuditnew,KUAF k,KUAF g WHERE k.ID = DAuditnew.Performerid AND k.GroupID = g.ID AND upper(DAuditnew.AuditStr)='LOGIN' and k.Deleted = '0' AND DAuditnew.AuditDate >'01-JAN-80'GROUP BY DAuditnew.performerID, k.FirstName, k.LastName, k.title, g.name
Mary_Slane_Corona
Thank you both for this report.
