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

• [Oracle EBS] Purge Concurrent Request and/or Manager Data
• [Oracle EBS] Query to list Reports assigned to a Responsibility
• [Oracle EBS] Query to list Active Responsibilities assigned to Active User
• [Oracle EBS] GL Journals Stuck With Funds Status In Process
• [Oracle EBS] Fixed Asset Error APP-OFA-48392 in Asset Workbench
• [Oracle EBS] ORA-01591 In-Doubt Transactions
• [Oracle EBS] Query to list Reports assigned to a Responsibility
• [Oracle EBS] Query Period Status
• [Oracle Siebel CRM] Query Last User Active
• [PostgreSQL] Truncate Table and Reset Sequence