Sod Summary we are not getting Exact data and Exact count(number different from UI) in production

[PreethiPandi]

Hi Team,

We are trying to extract the report from SOD Summary.

Report should contain: RISK CODE, Function, USERNAME and ACCOUNT NAME for open status and ruleset belongs to PG_SAP_S4HANA_PROD_100_Ruleset_2023_02

Using Query we are trying the extract the data count if different and not getting the account name In UI count it shows Different

Please find the below query which we have used:

select distinct sr.riskcode,u.username,a.name from rulesets rule,sodrisks sr

inner join users u on sr.userkey = u.userkey

inner join user_accounts ua on ua.userkey = u.userkey

inner join accounts a on a.accountkey = ua.accountkey

where rule.ruleset = 'PG_SAP_S4HANA_PROD_100_Ruleset_2023_02' and sr.status = 1

Could you please look in to the issue.

Regards,

Preethi

HE-IAM Team

[sai_sp]

@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.

[PreethiPandi]

Hi @sai_sp ,

I am using Saviynt v5.5SP3

And count for open is 238101

[sai_sp]

@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

[PreethiPandi]

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

[rushikeshvartak]

SELECT COUNT(sodkey) AS 'totalCount' FROM sodrisks WHERE status=1 AND
SODKEY IN (SELECT SODKEY FROM sodrisk_entitlement);

[rushikeshvartak]

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;

[PreethiPandi]

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

[Rajesh-R]

@PreethiPandi 

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;

[PreethiPandi]

Hi @Rajesh-R ,

The above query is displaying only the users belongs to admin and it is not returning all the users.

Regards,

Preethi

[Rajesh-R]

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;

[PreethiPandi]

Hi @Rajesh-R ,

Still we are not getting exact count almost 20k records are missing.

Regards,

Preethi