Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

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

PreethiPandi
New Contributor
New Contributor
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
11 REPLIES 11

sai_sp
Saviynt Employee
Saviynt Employee

@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
New Contributor
New Contributor

Hi @sai_sp ,

I am using Saviynt v5.5SP3

And count for open is 238101

PreethiPandi_0-1699282079831.png

 

@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

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
All-Star
All-Star

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


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

rushikeshvartak
All-Star
All-Star

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;


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

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
Saviynt Employee
Saviynt Employee

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

 


Thanks
Rajesh Ramalingam
Saviynt India

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
Saviynt Employee
Saviynt Employee

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;

 


Thanks
Rajesh Ramalingam
Saviynt India

Hi @Rajesh-R ,

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

Regards,

Preethi