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

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