Active User Details

nikhil.sanghvi

Hi All,

I am developing a report to get the result of all the active users. THe report also gives detail whether the user is disabled on CS, the last time user logged into the CS and the User creation on the CS. Below is the query whcih I have built,

select
K.id as id, K.name as USERID, K.FIRSTNAME||' '||K.LASTNAME as name, K.USERPRIVILEGES as ACCOUNT_DETAILS,
(select max(AUDITDATE) from DAUDITNEW where AUDITID='23' and PERFORMERID=K.id) as "Last Login",
(select max(AUDITDATE) from DAUDITNEW where AUDITID='1' and PERFORMERID=K.id) as "Creation Date"
from KUAF K
where K.type='0' and K.DELETED='0'

But the issue is, when I run the LR or WR, the report goes on running and show network error after a while. Also I tried running the query on the SQL developer directly hitting the database and nothing shows up.
If I remove the Last Login and Createion date from the SQL then everything starts working fine. So is the issue with the DAUDITNEW table performance on the DB or the SQL query it not written properly?

Thanks,
Nikhil Sanghvi

Giovanny Buitrago

Hi Nikhil,

I ran this report in SQL Server and it works, please see below:

 

select
K.ID as id, K.Name as USERID, 
K.FirstName + ' '+ K.LastName as name,
K.UserPrivileges as ACCOUNT_DETAILS,
(select max(X.AuditDate) from DAuditNew X where X.AuditID='23' and X.PerformerID=K.ID) as "Last Login",
(select max(Y.AuditDate) from DAuditNew Y where Y.AuditID='1' and Y.PerformerID=K.ID) as "Creation Date"
from KUAF K
where K.Type='0' and K.Deleted='0'