SOD violation summary analytics

[User_ID_Singh1]

Hi,

We have a requirement where we need to create a report for SOD Violations in which we see it in SOD Tab. Can anyone please help me if you have query related to this?. I have created a query according to requirement but there is data mismatch in some of the columns.

Can anyone please help if you have any queries related to this? I have also attached query which i have created.

thanks.

Columns:

1	Mitigating Control	15	Parent Role
2	Mitigating Control Description	16	Ruleset
3	Risk Name	17	Risk Status
4	Risk Description	18	Sod Violation Status
5	Priority	19	Risk Start Date
6	Endpoint	20	Risk End Date
7	Account Name	21	Reference Indicator
8	User Group	22	user displayName
9	Account Type	23	Description
10	User ID	24	Function Name
11	Actual vs Potential	25	Function Type
12	Role Name	26	MC Expiry Date
13	Risk Type	27	Risk assignment date
14	Associated Role Count	28	Risk Expired date

[Raghu]

@User_ID_Singh1  please find below article help

https://forums.saviynt.com/t5/saviynt-knowledge-base/sample-sod-report-analytic-queries/ta-p/50689

https://forums.saviynt.com/t5/saviynt-knowledge-base/sod-report-queries-part-ii/ta-p/50692

https://forums.saviynt.com/t5/identity-governance/sod-reports/m-p/36489

[User_ID_Singh1]

Hi Raghu,
For below query we need one week data, I have tried some date filters its not working. Can you help me here.

SELECT
sd.riskcode as 'Risk Name',
r.description as 'Risk Description',
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',
ep.endpointname as 'Endpoint',
acc.name as 'Account Name',
acc.usergroup as 'user group',
u.username as 'User ID',
sd.actualrisk as 'Actual vs Potential',
ev.entitlement_value as 'Role Name',
(case when r.risktype=1 then 'SOD' when r.risktype=2 then 'Conflict' END) as 'Risk Type',
se.PARENTROLEASCSV as 'Parent Role',
ru.RULESET as 'Ruleset',
CASE
WHEN sd.status = 1 THEN 'Open'
WHEN sd.status = 2 THEN 'In Progress'
WHEN sd.status = 3 THEN 'Risk Accepted'
WHEN sd.status = 4 THEN 'Closed'
WHEN sd.status = 5 THEN 'Remediated'
END AS 'Violation Status',
sd.FIRSTIMPORTDATE as 'Risk Start Date',
sd.RENDDATE as 'Risk End Date',
u.departmentnumber as 'Refernce Indicator',
u.displayname as 'Display Name',
r.description as 'Description',
f.function_name as 'Function Name',
f.FUNCTIONTYPE as 'Function Type'
FROM
sodrisks sd
LEFT JOIN users u ON (sd.userkey = u.userkey)
LEFT JOIN risks r ON (sd.riskkey = r.riskid)
LEFT JOIN busprocs bp ON (r.bpkey = bp.busprockey)
LEFT JOIN riskowners ro ON (r.riskid = ro.riskid)
LEFT JOIN users uu ON (ro.OWNERUSERKEY = uu.userkey)
LEFT JOIN user_groups ug ON (ro.OWNERUSERGROUPKEY = ug.USERGROUPKEY)
LEFT JOIN sodrisk_entitlement se ON (sd.sodkey = se.sodkey)
LEFT JOIN accounts acc ON (se.accountkey = acc.accountkey)
LEFT JOIN endpoints ep ON (acc.endpointkey = ep.endpointkey)
LEFT JOIN functions f ON (se.functionkey = f.functionkey)
left join rulesets ru on ru.rulesetkey = r.RULESETKEY
LEFT JOIN entitlement_values ev ON (se.tcodekey = ev.ENTITLEMENT_VALUEKEY)

[rushikeshvartak · Accepted solution]

SELECT
sd.riskcode as 'Risk Name',
r.description as 'Risk Description',
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',
ep.endpointname as 'Endpoint',
acc.name as 'Account Name',
acc.usergroup as 'user group',
u.username as 'User ID',
sd.actualrisk as 'Actual vs Potential',
ev.entitlement_value as 'Role Name',
(CASE WHEN r.risktype=1 THEN 'SOD' WHEN r.risktype=2 THEN 'Conflict' END) as 'Risk Type',
se.PARENTROLEASCSV as 'Parent Role',
ru.RULESET as 'Ruleset',
CASE
WHEN sd.status = 1 THEN 'Open'
WHEN sd.status = 2 THEN 'In Progress'
WHEN sd.status = 3 THEN 'Risk Accepted'
WHEN sd.status = 4 THEN 'Closed'
WHEN sd.status = 5 THEN 'Remediated'
END AS 'Violation Status',
sd.FIRSTIMPORTDATE as 'Risk Start Date',
sd.RENDDATE as 'Risk End Date',
u.departmentnumber as 'Refernce Indicator',
u.displayname as 'Display Name',
r.description as 'Description',
f.function_name as 'Function Name',
f.FUNCTIONTYPE as 'Function Type'
FROM
sodrisks sd
LEFT JOIN users u ON (sd.userkey = u.userkey)
LEFT JOIN risks r ON (sd.riskkey = r.riskid)
LEFT JOIN busprocs bp ON (r.bpkey = bp.busprockey)
LEFT JOIN riskowners ro ON (r.riskid = ro.riskid)
LEFT JOIN users uu ON (ro.OWNERUSERKEY = uu.userkey)
LEFT JOIN user_groups ug ON (ro.OWNERUSERGROUPKEY = ug.USERGROUPKEY)
LEFT JOIN sodrisk_entitlement se ON (sd.sodkey = se.sodkey)
LEFT JOIN accounts acc ON (se.accountkey = acc.accountkey)
LEFT JOIN endpoints ep ON (acc.endpointkey = ep.endpointkey)
LEFT JOIN functions f ON (se.functionkey = f.functionkey)
LEFT JOIN rulesets ru on ru.rulesetkey = r.RULESETKEY
LEFT JOIN entitlement_values ev ON (se.tcodekey = ev.ENTITLEMENT_VALUEKEY)
WHERE
sd.FIRSTIMPORTDATE >= CURDATE() - INTERVAL 7 DAY
ORDER BY
sd.FIRSTIMPORTDATE DESC;

[rushikeshvartak]

Did above query worked ? @User_ID_Singh1 

[User_ID_Singh1]

@rushikeshvartak Yes

[rushikeshvartak]

✅Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question and hit 'Kudos' button 👍.

You can create new post for new issues 

[User_ID_Singh1]

@rushikeshvartak  For this below query, am trying to add one week filter its not working. Can you help me here:

SELECT mco.MITIGATINGCONTROL as 'Mitigating Control',mco.MITIGATINGCONTROLDESCRIPTION as 'Mitigating Control Description', r.riskname as 'Risk Name', r.description as 'Risk Description',
u.username as 'USER ID',
ev.entitlement_value as 'Role Name',
se.PARENTROLEASCSV as 'Parent Role',
sd.actualrisk as 'Actual vs Potential',CASE
WHEN sd.status = 1 THEN 'Open'
WHEN sd.status = 2 THEN 'In Progress'
WHEN sd.status = 3 THEN 'Risk Accepted'
WHEN sd.status = 4 THEN 'Closed'
WHEN sd.status = 5 THEN 'Remediated'
END AS 'Violation Status',(case when r.risktype=1 then 'SOD' when r.risktype=2 then 'Conflict' END) as 'Risk Type', rs.ruleset as 'Ruleset',u.departmentnumber as 'Refernce Indicator',mco.expirydate,
u.displayname as 'Display 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', mc.startdate as 'Start Date', mc.enddate as 'End Date', f1.function_name as 'Function1', f2.function_name as 'Function2', u.username as 'User Name', f1.functiontype FROM mc_risk_account mc INNER JOIN risks r ON (mc.riskid = r.riskid) INNER JOIN rulesets rs ON (rs.rulesetkey = r.rulesetkey) LEFT JOIN mitigatingcontrols mco on (mc.mitigatingcontrolkey = mco.mitigatingcontrolid) LEFT JOIN sodrisks sd ON (r.riskid = sd.riskkey) LEFT JOIN users u ON (mc.userkey = u.userkey) LEFT JOIN accounts acc on (mc.accountkey=acc.accountkey) LEFT JOIN sodrisk_entitlement se ON (sd.sodkey = se.sodkey) LEFT JOIN entitlement_values ev ON (se.tcodekey = ev.ENTITLEMENT_VALUEKEY)
LEFT JOIN functions f1 ON (r.FUNCTION2KEY = f1.functionkey) LEFT JOIN functions f2 ON (r.FUNCTION1KEY = f2.functionkey) WHERE mc.status =1 and sd.status=3 ORDER BY r.riskname desc;

[rushikeshvartak]

Check previous response.

[User_ID_Singh1]

Hi Raghu,
For below query we need one week data, I have tried some date filters its not working. Can you help me here.

SELECT mco.MITIGATINGCONTROL as 'Mitigating Control',mco.MITIGATINGCONTROLDESCRIPTION as 'Mitigating Control Description', r.riskname as 'Risk Name', r.description as 'Risk Description',
u.username as 'USER ID',
ev.entitlement_value as 'Role Name',
se.PARENTROLEASCSV as 'Parent Role',
sd.actualrisk as 'Actual vs Potential',CASE
WHEN sd.status = 1 THEN 'Open'
WHEN sd.status = 2 THEN 'In Progress'
WHEN sd.status = 3 THEN 'Risk Accepted'
WHEN sd.status = 4 THEN 'Closed'
WHEN sd.status = 5 THEN 'Remediated'
END AS 'Violation Status',(case when r.risktype=1 then 'SOD' when r.risktype=2 then 'Conflict' END) as 'Risk Type', rs.ruleset as 'Ruleset',u.departmentnumber as 'Refernce Indicator',mco.expirydate,
u.displayname as 'Display 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', mc.startdate as 'Start Date', mc.enddate as 'End Date', f1.function_name as 'Function1', f2.function_name as 'Function2', u.username as 'User Name', f1.functiontype FROM mc_risk_account mc INNER JOIN risks r ON (mc.riskid = r.riskid) INNER JOIN rulesets rs ON (rs.rulesetkey = r.rulesetkey) LEFT JOIN mitigatingcontrols mco on (mc.mitigatingcontrolkey = mco.mitigatingcontrolid) LEFT JOIN sodrisks sd ON (r.riskid = sd.riskkey) LEFT JOIN users u ON (mc.userkey = u.userkey) LEFT JOIN accounts acc on (mc.accountkey=acc.accountkey) LEFT JOIN sodrisk_entitlement se ON (sd.sodkey = se.sodkey) LEFT JOIN entitlement_values ev ON (se.tcodekey = ev.ENTITLEMENT_VALUEKEY)
LEFT JOIN functions f1 ON (r.FUNCTION2KEY = f1.functionkey) LEFT JOIN functions f2 ON (r.FUNCTION1KEY = f2.functionkey) WHERE mc.status =1 and sd.status=3 ORDER BY r.riskname desc;

[rushikeshvartak]

SELECT 
    mco.MITIGATINGCONTROL as 'Mitigating Control',
    mco.MITIGATINGCONTROLDESCRIPTION as 'Mitigating Control Description',
    r.riskname as 'Risk Name', 
    r.description as 'Risk Description',
    u.username as 'USER ID',
    ev.entitlement_value as 'Role Name',
    se.PARENTROLEASCSV as 'Parent Role',
    sd.actualrisk as 'Actual vs Potential',
    CASE
        WHEN sd.status = 1 THEN 'Open'
        WHEN sd.status = 2 THEN 'In Progress'
        WHEN sd.status = 3 THEN 'Risk Accepted'
        WHEN sd.status = 4 THEN 'Closed'
        WHEN sd.status = 5 THEN 'Remediated'
    END AS 'Violation Status',
    (CASE 
        WHEN r.risktype=1 THEN 'SOD' 
        WHEN r.risktype=2 THEN 'Conflict' 
    END) as 'Risk Type', 
    rs.ruleset as 'Ruleset',
    u.departmentnumber as 'Refernce Indicator',
    mco.expirydate,
    u.displayname as 'Display 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', 
    mc.startdate as 'Start Date', 
    mc.enddate as 'End Date', 
    f1.function_name as 'Function1', 
    f2.function_name as 'Function2', 
    u.username as 'User Name', 
    f1.functiontype 
FROM 
    mc_risk_account mc 
INNER JOIN 
    risks r ON (mc.riskid = r.riskid) 
INNER JOIN 
    rulesets rs ON (rs.rulesetkey = r.rulesetkey) 
LEFT JOIN 
    mitigatingcontrols mco on (mc.mitigatingcontrolkey = mco.mitigatingcontrolid) 
LEFT JOIN 
    sodrisks sd ON (r.riskid = sd.riskkey) 
LEFT JOIN 
    users u ON (mc.userkey = u.userkey) 
LEFT JOIN 
    accounts acc on (mc.accountkey=acc.accountkey) 
LEFT JOIN 
    sodrisk_entitlement se ON (sd.sodkey = se.sodkey) 
LEFT JOIN 
    entitlement_values ev ON (se.tcodekey = ev.ENTITLEMENT_VALUEKEY)
LEFT JOIN 
    functions f1 ON (r.FUNCTION2KEY = f1.functionkey) 
LEFT JOIN 
    functions f2 ON (r.FUNCTION1KEY = f2.functionkey) 
WHERE 
    mc.status = 1 
    AND sd.status = 3 
    AND mc.startdate >= NOW() - INTERVAL 7 DAY
ORDER BY 
    r.riskname DESC;

[User_ID_Singh1]

@rushikeshvartak above query got saved. But when i ran the report after waiting 20 mins its not giving me data in Analytics history page.

[rushikeshvartak]

SELECT 
    mco.MITIGATINGCONTROL as 'Mitigating Control',
    mco.MITIGATINGCONTROLDESCRIPTION as 'Mitigating Control Description',
    r.riskname as 'Risk Name', 
    r.description as 'Risk Description',
    u.username as 'USER ID',
    ev.entitlement_value as 'Role Name',
    se.PARENTROLEASCSV as 'Parent Role',
    sd.actualrisk as 'Actual vs Potential',
    CASE
        WHEN sd.status = 1 THEN 'Open'
        WHEN sd.status = 2 THEN 'In Progress'
        WHEN sd.status = 3 THEN 'Risk Accepted'
        WHEN sd.status = 4 THEN 'Closed'
        WHEN sd.status = 5 THEN 'Remediated'
    END AS 'Violation State',
    (CASE 
        WHEN r.risktype=1 THEN 'SOD' 
        WHEN r.risktype=2 THEN 'Conflict' 
    END) as 'Risk Type', 
    rs.ruleset as 'Ruleset',
    u.departmentnumber as 'Refernce Indicator',
    mco.expirydate,
    u.displayname as 'Display 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', 
    mc.startdate as 'Start Dat', 
    mc.enddate as 'End Dat', 
    f1.function_name as 'Function1', 
    f2.function_name as 'Function2', 
    u.username as 'User Name', 
    f1.functiontype 
FROM 
    mc_risk_account mc 
INNER JOIN 
    risks r ON (mc.riskid = r.riskid) 
INNER JOIN 
    rulesets rs ON (rs.rulesetkey = r.rulesetkey) 
LEFT JOIN 
    mitigatingcontrols mco on (mc.mitigatingcontrolkey = mco.mitigatingcontrolid) 
LEFT JOIN 
    sodrisks sd ON (r.riskid = sd.riskkey) 
LEFT JOIN 
    users u ON (mc.userkey = u.userkey) 
LEFT JOIN 
    accounts acc on (mc.accountkey=acc.accountkey) 
LEFT JOIN 
    sodrisk_entitlement se ON (sd.sodkey = se.sodkey) 
LEFT JOIN 
    entitlement_values ev ON (se.tcodekey = ev.ENTITLEMENT_VALUEKEY)
LEFT JOIN 
    functions f1 ON (r.FUNCTION2KEY = f1.functionkey) 
LEFT JOIN 
    functions f2 ON (r.FUNCTION1KEY = f2.functionkey) 
WHERE 
    mc.status = 1 
    AND sd.status = 3 
    AND mc.startdate >= NOW() - INTERVAL 7 DAY
ORDER BY 
    r.riskname DESC;

[User_ID_Singh1]

@rushikeshvartak  Still not getting saved.

[rushikeshvartak]

Check logs

[User_ID_Singh1]

@rushikeshvartak Checked logs, no error found in it.

[rushikeshvartak]

Share logs 

[User_ID_Singh1]

@rushikeshvartak 
I have raised a request with SOD Violations and Added Mitigating Controls. I have also completed request and tasks related to it.Now, when i run the report, its showing Mitigating controls column as empty and for older records like two days back i can see mitigating controls for that records. Please let me know if am missing something
Below is the query:
SELECT
mco.MITIGATINGCONTROL as 'Mitigating Control',mco.MITIGATINGCONTROLDESCRIPTION as 'Mitigating Control Description',
sd.riskcode as 'Risk Name',
r.description as 'Risk Description',acc.accounttype,
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.status=1 then 'In Active' when r.status =0 then 'Active' End) as 'Risk Status',
ep.endpointname as 'Endpoint',
acc.name as 'Account Name',
acc.usergroup as 'user group',ep.endpointname,
u.username as 'User ID',
sd.actualrisk as 'Actual vs Potential',
ev.entitlement_value as 'Role Name',
(CASE WHEN r.risktype=1 THEN 'SOD' WHEN r.risktype=2 THEN 'Conflict' END) as 'Risk Type',
se.PARENTROLEASCSV as 'Parent Role',
ru.RULESET as 'Ruleset',
CASE
WHEN sd.status = 1 THEN 'Open'
WHEN sd.status = 2 THEN 'In Progress'
WHEN sd.status = 3 THEN 'Risk Accepted'
WHEN sd.status = 4 THEN 'Closed'
WHEN sd.status = 5 THEN 'Remediated'
END AS 'Violation Status',
sd.FIRSTIMPORTDATE as 'Risk Start Date',
sd.RENDDATE as 'Risk End Date',
u.departmentnumber as 'Refernce Indicator',
u.displayname as 'Display Name',
r.description as 'Description',
f.function_name as 'Function Name',
mco.expirydate,
f.FUNCTIONTYPE as 'Function Type'
FROM
sodrisks sd
left join mc_risk_account mc on sd.SODKEY=mc.SODRISKS
LEFT JOIN mitigatingcontrols mco on (mco.mitigatingcontrolid = mc.mitigatingcontrolkey)
LEFT JOIN users u ON (sd.userkey = u.userkey)
LEFT JOIN risks r ON (sd.riskkey = r.riskid)
LEFT JOIN sodrisk_entitlement se ON (sd.sodkey = se.sodkey)
LEFT JOIN accounts acc ON (se.accountkey = acc.accountkey)
LEFT JOIN endpoints ep ON (acc.endpointkey = ep.endpointkey)
LEFT JOIN functions f ON (se.functionkey = f.functionkey)
LEFT JOIN rulesets ru on ru.rulesetkey = r.RULESETKEY
LEFT JOIN entitlement_values ev ON (se.tcodekey = ev.ENTITLEMENT_VALUEKEY)
WHERE
sd.FIRSTIMPORTDATE >= CURDATE() - INTERVAL 7 DAY
ORDER BY
sd.FIRSTIMPORTDATE DESC;

[rushikeshvartak]

• If initial thread answer is resolved please create new thread for new issue. As thread subject and ask is getting changed and its confusing.
• added mitigation from request table will be stored in another table. above report is for detective SoD and migration and not for preventive
• request_exceptions stored preventive soil mitigation control information 

[rushikeshvartak]

Can you share screenshots of data which is coming wrong to narrow down issue