[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 :