[Oracle Siebel CRM] Query Last User Active

Oracle Siebel CRM 8.1 use dba_users table for the application user. But the user activitiy history is stored into table at siebel schema. Here the query to get the user last active / last login date.


SELECT LOGIN, MAX(TANGGAL) LAST_TRANS_DATE
FROM (
SELECT S.LOGIN, MAX(TRUNC(PEL.LAST_UPD)) TANGGAL
FROM SIEBEL.S_ASSET_RDNG PEL, SIEBEL.S_USER S
WHERE PEL.LAST_UPD_BY =S.ROW_ID
AND EXISTS (SELECT 1 FROM DBA_USERS D WHERE D.USERNAME = S.LOGIN AND D.ACCOUNT_STATUS='OPEN')
GROUP BY S.LOGIN
UNION
SELECT S.LOGIN, MAX(TRUNC(PEL.LAST_UPD)) TANGGAL
FROM SIEBEL.S_ORG_EXT PEL, SIEBEL.S_USER S
WHERE PEL.LAST_UPD_BY =S.ROW_ID
AND EXISTS (SELECT 1 FROM DBA_USERS D WHERE D.USERNAME = S.LOGIN AND D.ACCOUNT_STATUS='OPEN')
GROUP BY S.LOGIN
UNION
SELECT UPPER(SRVR_USER_NAME) USERNAME, MAX(TRUNC(CREATED)) TANGGAL_LAST   FROM siebel.s_srm_task_hist
WHERE  UPPER(SRVR_USER_NAME) IN (SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS ='OPEN')
GROUP BY UPPER(SRVR_USER_NAME)
--ORDER BY 2
UNION
select c.username,MAX(TRUNC(a.SAMPLE_TIME))
from DBA_HIST_ACTIVE_SESS_HISTORY a,  dba_users c
where a.user_id=c.user_id
AND C.ACCOUNT_STATUS='OPEN'
AND C.USERNAME IN (SELECT LOGIN FROM SIEBEL.S_USER)
GROUP BY C.USERNAME
)
GROUP BY LOGIN


Artikel Terkait :

1 comments:

Tejaswi Ramavathu said...

I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle Siebel CRM, kindly contact us http://www.maxmunus.com/contact
MaxMunus Offer World Class Virtual Instructor led training on Oracle Siebel CRM. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
For Demo Contact us.
Nitesh Kumar
MaxMunus
E-mail: nitesh@maxmunus.com
Skype id: nitesh_maxmunus
Ph:(+91) 8553912023
http://www.maxmunus.com/