Handy SAP IDM SQL Queries - Related to monitoring & Reporting activities

[devaprakash_b]

Hello Everyone,

The below sql queries have always come in handy for me whenever i need to trouble shoot an issue or else need to generate reporting or monitoring based reports to identify rootcause of problems with ease. Hope it might be helpful for you too.

 

1. To identify the root role via which the inherited privileges are assigned to the users

SELECT t1.MCUNIQUEID linkid,T1.MCTHISMSKEYVALUE userid,T1.MCOTHERMSKEYVALUE privid,T3.MCOTHERMSKEYVALUE roleid FROM IDMV_LINK_EXT2 T1 

LEFT JOIN MXI_ROOT_REFERENCE T2 ON T1.MCUNIQUEID = T2.MCTHISLINKID

LEFT JOIN IDMV_LINK_EXT2 T3 ON T2.MCROOTLINKID = T3.MCUNIQUEID

WHERE T1.MCATTRNAME = 'MXREF_MX_PRIVILEGE' AND t1.MCASSIGNEDDIRECT = 0  AND T3.MCATTRNAME = 'MXREF_MX_ROLE' AND T1.MCTHISMSKEYVALUE = <UserMskeyvalue here>

ORDER BY t1.MCOTHERMSKEYVALUE

 

 

2. To identify the rootcause/reason for all failed assignments

SELECT MCUNIQUEID,MCTHISMSKEY,MCTHISMSKEYVALUE,MCOTHERMSKEY,MCOTHERMSKEYVALUE,MCATTRNAME,

IDMV_VALUE_BASIC.SEARCHVALUE,MCASSIGNER,MCADDEDTIME,MCVALIDFROM,MCVALIDTO,MCREASON,MCASSIGNEDDIRECT, MCASSIGNEDDYNAMICGROUP,MCORPHAN,MCDIRTY,MSG

FROM IDMV_LINK_EXT2

LEFT JOIN MXP_AUDIT ON MCADDAUDIT = MXP_AUDIT.AUDITID

LEFT JOIN IDMV_VALUE_BASIC ON MCOTHERMSKEY = IDMV_VALUE_BASIC.MSKEY

WHERE MCEXECSTATE = 4 AND IDMV_VALUE_BASIC.ATTRNAME = 'MX_REPOSITORYNAME' 

ORDER BY MCTHISMSKEY WITH UR	

 

3. To identify the rootcause/reason for failed provisioning jobs for last 30 days

SELECT T1.AUDITID AUDITID,T1.TASKID TASKID,T3.TASKNAME TASKNAME,T1.MSKEY ENTRYMSKEY,T2.MCMSKEYVALUE ENTRYMSKEYVALUE,T2.MCDISPLAYNAME ENTRYDISPLAYNAME,

T1.POSTEDDATE EXECUTIONDATE,T4.NAME STATUS,T1.MSG ERRORMSG,T5.REP_NAME REPOSITORY,T1.USERID USERID,T6.IDSTORENAME  IDSTORE

FROM MXP_AUDIT T1 

LEFT JOIN IDMV_ENTRY_SIMPLE T2 ON T1.MSKEY = T2.MCMSKEY

LEFT JOIN MXP_TASKS T3 ON T1.TASKID = T3.TASKID

LEFT JOIN MXP_PROVSTATUS T4 ON T1.PROVSTATUS = T4.PROVSTATUS

LEFT JOIN MC_REPOSITORY T5 ON T1.REPOSITORY = T5.REP_ID

LEFT JOIN MXI_IDSTORES T6 ON T1.IDSID = T6.IS_ID

WHERE T1.PROVSTATUS IN (1001,1101) AND (T1.POSTEDDATE>(CURRENT_DATE-30))

ORDER BY T1.POSTEDDATE DESC WITH UR



For MSSQL database USE GETDATE() in place of CURRENT_DATE

 

Feel free to correct me incase of any mistakes and if your are looking for additional queries kindly let me know