Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/23/2023 11:39 PM
10/24/2023 08:53 AM
@PreethiPandi what is the difference in count? Also the way data shows up in UI is different from the query output. SOD shows at a user level and not at an account level in Saviynt. Is this a single system SOD or SAP Group? Do users have multiple accounts in the same endpoints?
Please also give us the version you are on.
11/06/2023 06:49 AM
11/07/2023 08:53 AM
@PreethiPandi is there a reason you are using the query and not the OOB features? Looks like the query is not giving the right results
11/13/2023 05:47 AM
Hi @sai_sp ,
Our requirement is we want to display the SOD Violations based on Open in the Dashboard.
When we click on the dashboard we need to view the analytics report with contains below fields
RISK CODE, Function, USERNAME and ACCOUNT NAME
count and data what is displayed in the SOD Violations page should be same in the report what we are extracting.
Regards,
Preethi
11/13/2023 11:28 AM
SELECT COUNT(sodkey) AS 'totalCount' FROM sodrisks WHERE status=1 AND
SODKEY IN (SELECT SODKEY FROM sodrisk_entitlement);
11/13/2023 11:29 AM
SELECT u.username, u.departmentname as 'Department', u.title, sr.Riskcode, rk.DESCRIPTION, CASE WHEN sr.status = 1 THEN 'NEW' END AS 'SoD Risk Status' FROM sodrisks sr inner JOIN risks rk ON sr.riskkey = rk.riskid inner join Users u on sr.userkey = u.userkey WHERE sr.status =1 group by u.username,sr.Riskcode;
11/15/2023 01:58 AM
Hi @rushikeshvartak ,
I have tried the above Two queries what you have posted Both also not giving the the Exact Count and I am looking for the Account also need to be added to the query.
I am looking for the below fields
RISK CODE, Function, USERNAME and ACCOUNT NAME
Please help us on this.
Regards,
Preethi
11/15/2023 03:33 AM - edited 11/15/2023 03:55 AM
Looking at the query - there might be some orphan accounts which might cause the gap. Please use the following and chk if this solves your use case.
select distinct sr.riskcode,u.username,a.name from rulesets rule
inner join risks r on rule.RULESETKEY=r.RULESETKEY
inner join sodrisks sr on sr.RISKKEY=r.RISKID
inner join sodrisk_entitlement sre on sr.SODKEY=sre.SODKEY
inner join users u on sr.userkey = u.userkey
inner join accounts a on a.accountkey = sre.accountkey
where rule.ruleset = 'PG_SAP_S4HANA_PROD_100_Ruleset_2023_02' and sr.status = 1;
11/15/2023 03:49 AM
Hi @Rajesh-R ,
The above query is displaying only the users belongs to admin and it is not returning all the users.
Regards,
Preethi
11/15/2023 04:00 AM
Please try this:
select distinct sr.riskcode,u.username,a.name from rulesets rule
inner join risks r on rule.RULESETKEY=r.RULESETKEY
inner join sodrisks sr on sr.RISKKEY=r.RISKID
inner join sodrisk_entitlement sre on sr.SODKEY=sre.SODKEY
inner join users u on sr.userkey = u.userkey
inner join accounts a on a.accountkey = sre.accountkey
where rule.ruleset = 'PG_SAP_S4HANA_PROD_100_Ruleset_2023_02' and sr.status = 1;
11/17/2023 06:06 AM