Hi Team,
We are currently sending a monthly SOD summary report to the client, configured in analytics using the query below to retrieve the data. Please note that this query only captures details related to role type entitlements.
We now have a requirement to provide a detailed SOD report that includes child permission details. Any guidance on modifying the analytics query or suggestions on how to achieve this would be greatly appreciated so we can send the detailed SOD report to the client each month
SELECT rule.Ruleset as 'RuleSet', sr.riskcode as 'Risk Name', r.description as 'Risk Description', GROUP_CONCAT(DISTINCT f.FUNCTION_NAME) AS 'Function Name', CASE WHEN r.priority = 0 THEN 'Medium' WHEN r.priority = 1 THEN 'High' WHEN r.priority = 2 THEN 'Low' WHEN r.priority = 3 THEN 'Critical' WHEN r.priority = 4 THEN 'Very Low' ELSE 'None' END AS 'Risk Priority', CASE WHEN r.risktype = 1 THEN 'SOD' ELse 'Critical Access' END AS 'RISK TYPE', CASE WHEN sr.status = 1 THEN 'New' WHEN sr.status = 2 THEN 'In Progress' WHEN sr.status = 3 THEN 'Risk Accepted' WHEN sr.status = 4 THEN 'Closed' WHEN sr.status = 5 THEN 'Remediated' ELSE 'None' END AS 'Violation Status', GROUP_CONCAT(DISTINCT ACC.customproperty5) AS NAME,GROUP_CONCAT(DISTINCT ACC.NAME) AS Account_Name, GROUP_CONCAT(DISTINCT ACC.Status) AS 'K8AccountStatus',ACC.customproperty2 as EMAIL_ADDRESS,GROUP_CONCAT(DISTINCT ACC.customproperty4) AS JOB_TITLE, GROUP_CONCAT(DISTINCT U.OWNER) AS MANAGER, (select u1.displayname from users u1 where u1.userkey=u.manager) as 'Manager Display Name', (select u2.title from users u2 where u2.userkey=u.manager) as 'Manager Job Title', (select endpointname from endpoints where ENDPOINTKEY=acc.endpointkey) as 'Application Name', GROUP_CONCAT(DISTINCT EV.ENTITLEMENT_VALUE) AS K8_ENTITLEMENT, sr.comments as 'Comment' FROM rulesets rule, SODRISKS SR, USERS U, RISKS R, SODRISK_ENTITLEMENT SRE, ACCOUNTS ACC, ENTITLEMENT_VALUES EV , SODRISK_ENTITLEMENT se, functions f WHERE r.RULESETKEY = rule.RULESETKEY AND SR.RISKKEY = R.RISKID AND SR.SODKEY = SRE.SODKEY AND SR.USERKEY = U.USERKEY AND sr.sodkey = se.SODKEY AND se.FUNCTIONKEY=f.FUNCTIONKEY AND SRE.ACCOUNTKEY = ACC.ACCOUNTKEY AND SRE.ASSOCIATEDSAPROLEKEY = EV.ENTITLEMENT_VALUEKEY AND sr.status in (1) AND rule.ruleset like 'K8_SOD' GROUP BY SRE.SODKEY
Thank you,
Varsha
