Click HERE to see how Saviynt Intelligence is transforming the industry. |
06/30/2024 11:46 PM
Hello,
can someone help me to add a where clause for the SOD Ruleset in the following query please?
I need to update the mitigating control review, so only the risks from the Ruleset with key 14 are shown.
Thank you and BR, Michael
select ControlOwners.userkey as 'USERCONTEXT', ControlOwner as 'Country Control Owner',concat(ControlOwners.USER_GROUPDESCRIPTION," (",ControlOwners.USER_GROUPNAME,")") as 'MC Group Country', MRA.Riskname as 'Risk Name',
MRA.Description as 'Risk Desc',SR.Actualrisk as 'Risk Type',SR.username as 'Violating User',MRA.STARTDATE as 'Mitigating Control Startdate',MRA.ENDDATE as 'Mitigating Control Endate', ORG.customproperty20 as 'User Company Code'
from
(select u.username as ControlOwner,ug.USER_GROUPNAME,ug.USER_GROUPDESCRIPTION,ug.USERGROUPKEY,u.userkey
from users u, user_groups ug, usergroup_users ugu
where u.userkey = ugu.USERKEY and ugu.USER_GROUPKEY = ug.USERGROUPKEY) As ControlOwners
LEFT JOIN
(select u1.username as CFO,ug1.USER_GROUPNAME,ug1.USERGROUPKEY,u1.userkey,u1.country
from users u1, user_groups ug1, usergroup_owners ugo
where u1.userkey = ugo.USERKEY and ugo.USERGROUPKEY = ug1.USERGROUPKEY) as CFO on ControlOwners.USERGROUPKEY = CFO.USERGROUPKEY
LEFT JOIN
(select mc.MITIGATINGCONTROL,r.riskname,r.DESCRIPTION ,mra.APPROVERKEY, r.riskid,mra.STARTDATE,mra.ENDDATE,mra.ID
from mitigatingcontrols mc,mc_risk_account mra, users app, users u2, risks r
where app.userkey = mra.APPROVERKEY
and u2.userkey = mra.userkey and mra.riskid = r.RISKID
and mc.MITIGATINGCONTROLID = mra.MITIGATINGCONTROLKEY) as MRA on CFO.userkey = MRA.APPROVERKEY
LEFT JOIN
(select sr.Actualrisk,u3.userkey,u3.username,sr.MC_RISK_ACCOUNTKEY
from sodrisks sr, users u3
where sr.status=3
and u3.userkey = sr.userkey) as SR on SR.MC_RISK_ACCOUNTKEY = MRA.ID
LEFT JOIN
(select u4.userkey,u4.username,u4.customproperty20, u4.statuskey
from users u4) as ORG on ORG.userkey = SR.userkey
where (sr.Actualrisk = 'Actual Risk' or sr.Actualrisk = 'Potential Risk')
and ORG.customproperty20 = 'PIPE-AT'
and MRA.MitigatingControl = 'Inventory Postings (MB51)'
and ControlOwners.USER_GROUPNAME = 'PIPE-AT Inventory Postings (MB51)'
and ControlOwner ='atblumar'
and ORG.statuskey='1';
Solved! Go to Solution.
07/01/2024 12:07 AM
Hi @MichaelSchmid can you try the below query?
SELECT
ControlOwners.userkey AS 'USERCONTEXT',
ControlOwner AS 'Country Control Owner',
CONCAT(ControlOwners.USER_GROUPDESCRIPTION,
' (',
ControlOwners.USER_GROUPNAME,
')') AS 'MC Group Country',
MRA.Riskname AS 'Risk Name',
MRA.Description AS 'Risk Desc',
SR.Actualrisk AS 'Risk Type',
SR.username AS 'Violating User',
MRA.STARTDATE AS 'Mitigating Control Startdate',
MRA.ENDDATE AS 'Mitigating Control Endate',
ORG.customproperty20 AS 'User Company Code'
FROM
(SELECT
u.username AS ControlOwner,
ug.USER_GROUPNAME,
ug.USER_GROUPDESCRIPTION,
ug.USERGROUPKEY,
u.userkey
FROM
users u, user_groups ug, usergroup_users ugu
WHERE
u.userkey = ugu.USERKEY
AND ugu.USER_GROUPKEY = ug.USERGROUPKEY) AS ControlOwners
LEFT JOIN
(SELECT
u1.username AS CFO,
ug1.USER_GROUPNAME,
ug1.USERGROUPKEY,
u1.userkey,
u1.country
FROM
users u1, user_groups ug1, usergroup_owners ugo
WHERE
u1.userkey = ugo.USERKEY
AND ugo.USERGROUPKEY = ug1.USERGROUPKEY) AS CFO ON ControlOwners.USERGROUPKEY = CFO.USERGROUPKEY
LEFT JOIN
(SELECT
mc.MITIGATINGCONTROL,
r.riskname,
r.DESCRIPTION,
mra.APPROVERKEY,
r.riskid,
mra.STARTDATE,
mra.ENDDATE,
mra.ID
FROM
mitigatingcontrols mc, mc_risk_account mra, users app, users u2, risks r
WHERE
app.userkey = mra.APPROVERKEY
AND u2.userkey = mra.userkey
AND mra.riskid = r.RISKID
AND mc.MITIGATINGCONTROLID = mra.MITIGATINGCONTROLKEY
AND r.rulesetkey = 13) AS MRA ON CFO.userkey = MRA.APPROVERKEY
LEFT JOIN
(SELECT
sr.Actualrisk,
u3.userkey,
u3.username,
sr.MC_RISK_ACCOUNTKEY
FROM
sodrisks sr, users u3
WHERE
sr.status = 3
AND u3.userkey = sr.userkey) AS SR ON SR.MC_RISK_ACCOUNTKEY = MRA.ID
LEFT JOIN
(SELECT
u4.userkey, u4.username, u4.customproperty20, u4.statuskey
FROM
users u4) AS ORG ON ORG.userkey = SR.userkey
WHERE
(sr.Actualrisk = 'Actual Risk'
OR sr.Actualrisk = 'Potential Risk')
AND ORG.customproperty20 = 'PIPE-AT'
AND MRA.MitigatingControl = 'Inventory Postings (MB51)'
AND ControlOwners.USER_GROUPNAME = 'PIPE-AT Inventory Postings (MB51)'
AND ControlOwner = 'atblumar'
AND ORG.statuskey = '1';