SoD violations email to users

[varunakarnia]

Hi All,

Is there any way we can send the list of all SoD violations detected from SoDworkbench in an email to specific users for a certain endpoint?

[NM · Accepted solution]

Hi @varunakarnia you can create an analytics report and send it via email.

[Raghu · Accepted solution]

@varunakarnia  below query may it will help to you

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

[rushikeshvartak · Accepted solution]

• Create analytics report 
• attach email template using below query
• SELECT sd.riskcode as 'Risk Name', r.description as'Risk Description', CASE WHEN r.priority = 0 THEN'Medium' WHEN r.priority = 1 THEN 'High' WHENr.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'Application', u.username ,u.firstname as 'User First Name', u.lastname as'User Last Name', CASE WHEN ro.owneruserkey isNULL THEN ug.user_groupname WHENro.ownerusergroupkey is NULL THEN u.username ELSE 'None' END AS 'Risk Owner', f.function_name as 'Function', se.PARENTROLEASCSV as 'Parent Entitlement', ev.entitlement_value as 'Child Entitlement', CASE WHEN sd.status = 1 THEN 'New'WHEN sd.status = 2 THEN 'In Progress' WHENsd.status = 3 THEN 'Risk Accepted' WHEN sd.status = 4 THEN 'Closed' WHEN sd.status = 5 THEN'Remediated' ELSE 'None' END AS 'Violation Status', bp.busprocname as 'Business Process'FROM sodrisks sd LEFT OUTER JOIN users u ON(sd.userkey = u.userkey) LEFT OUTER JOIN risks r ON (sd.riskkey = r.riskid) LEFT OUTER JOINbusprocs bp ON (r.bpkey = bp.busprockey) LEFTOUTER JOIN riskowners ro ON (r.riskid = ro.riskid) LEFT OUTER JOIN users uu ON(ro.OWNERUSERKEY = uu.userkey) LEFT OUTERJOIN user_groups ug ON(ro.OWNERUSERGROUPKEY =ug.USERGROUPKEY) LEFT OUTER JOINsodrisk_entitlement se ON (sd.sodkey = se.sodkey) LEFT OUTER JOIN accounts acc on(se.accountkey=acc.accountkey) LEFT OUTER JOINendpoints ep on(acc.endpointkey=ep.endpointkey) LEFT OUTERJOIN functions f ON (se.functionkey =f.functionkey) LEFT OUTER JOINentitlement_values ev ON (se.tcodekey =ev.ENTITLEMENT_VALUEKEY);