Report for SOD, rulset and functions having Entitlements

[gagan94]

Hi Team,

We need to create one report where we have to gather data for ruleset containing multiple risks and risks having functions which is associated with various entitlements. I had created one report where I am able to get till risks, not sure for how to reach at entitlements level, if anybody is having such report please help me out with this.

Query which I used: 

select rulesets.RULESET,risks.RISKNAME ,risks.STATUS as 'Risk-Status',sodrisks.comments,sodrisks.STATUS as 'SOD-Status' from rulesets,risks,sodrisks,sodrisk_entitlement where rulesets.RULESETKEY=risks.RULESETKEY and sodrisks.SODKEY =sodrisk_entitlement.SODKEY

Thanks and Regards,

Gagan Juneja

[rushikeshvartak]

Use below tables

Function - for fuctions details ( you need 2 like f1 & f2 as risk contains min 2 function) 

Function_entitlement - entitlement from function ( use entitlement_valuekey & join with entitlement_values table) 

[gagan94]

Hi Rushikesh,

Do we have any sql query for getting this data.

Thanks and Regards,

Gagan 

[rushikeshvartak · Accepted solution]

select distinct rule.ruleset ,r.riskname,f1.function_name f1,ev1.entitlement_value f1e1,f2.function_name f2,ev2.entitlement_value f2e2 from risks r ,functions f1,functions f2,function_entitlements fe1,function_entitlements fe2 ,entitlement_values ev1,entitlement_values ev2,entitlement_types et1,entitlement_types et2,endpoints ep1,rulesets rule where r.function1key=f1.functionkey and r.function2key=f2.functionkey and f1.functionkey=fe1.functionkey and f2.functionkey=fe2.functionkey and fe1.ENTITLEMENT_VALUEKEY=ev1.ENTITLEMENT_VALUEKEY and fe2.ENTITLEMENT_VALUEKEY=ev2.ENTITLEMENT_VALUEKEY and et1.ENTITLEMENTTYPEKEY=ev1.ENTITLEMENTTYPEKEY and et2.ENTITLEMENTTYPEKEY=ev2.ENTITLEMENTTYPEKEY and et1.ENDPOINTKEY=ep1.ENDPOINTKEY and rule.rulesetkey=r.rulesetkey 

[gagan94]

Hey Everyone,

@rushikeshvartak : When i tried to add one more column along with endpoint value  as It is giving me error 'Operand should contain 1 column, Is there any way to get two values.,

Requirement is to gather all entitlements of particular endpoints which are not covered in any of SOD's Risk, I am attaching my query for your reference.

select ev.ENTITLEMENT_VALUE,ep.endpointname from entitlement_values ev,entitlement_types et,endpoints ep where ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and et.endpointkey=ep.endpointkey and et.SYSTEMKEY=(select ss.SYSTEMKEY from securitysystems ss where ss.SYSTEMNAME='Endpointname') and ev.status=1 and ev.ENTITLEMENT_VALUE NOT IN((select distinct ev1.ENTITLEMENT_VALUE,ep1.endpointname from risks r ,functions f1,function_entitlements fe1,entitlement_values ev1,entitlement_types et1,endpoints ep1,rulesets rule where r.function1key=f1.functionkey and f1.functionkey=fe1.functionkey and fe1.ENTITLEMENT_VALUEKEY=ev1.ENTITLEMENT_VALUEKEY and et1.endpointkey=ep1.endpointkey and et1.ENTITLEMENTTYPEKEY=ev1.ENTITLEMENTTYPEKEY and rule.rulesetkey=r.rulesetkey UNION
select distinct ev2.ENTITLEMENT_VALUE,ep2.endpointname from risks r,functions f2,function_entitlements fe2,entitlement_values ev2,entitlement_types et2,endpoints ep2,rulesets rule where r.function2key=f2.functionkey and f2.functionkey=fe2.functionkey and fe2.ENTITLEMENT_VALUEKEY=ev2.ENTITLEMENT_VALUEKEY and et2.ENTITLEMENTTYPEKEY=ev2.ENTITLEMENTTYPEKEY and et2.ENDPOINTKEY=ep2.ENDPOINTKEY and rule.rulesetkey=r.rulesetkey))

[rushikeshvartak]

Use IN not =

[gagan94]

@rushikeshvartak : I tried this as well but still throwing error, Can you please run it and share the updated one that would be helpful for me.

Regards,

Gagan

[rushikeshvartak]

Below line is wrong 

Left Hand column not matching with RHS

ev.ENTITLEMENT_VALUE NOT IN((select distinct ev1.ENTITLEMENT_VALUE,ep1.endpointname

select ev.ENTITLEMENT_VALUE,ep.endpointname from entitlement_values ev,entitlement_types et,endpoints ep where ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and et.endpointkey=ep.endpointkey and et.SYSTEMKEY=(select ss.SYSTEMKEY from securitysystems ss where ss.SYSTEMNAME='Endpointname') and ev.status=1 and ev.ENTITLEMENT_VALUE NOT IN((select distinct ev1.ENTITLEMENT_VALUE from risks r ,functions f1,function_entitlements fe1,entitlement_values ev1,entitlement_types et1,endpoints ep1,rulesets rule where r.function1key=f1.functionkey and f1.functionkey=fe1.functionkey and fe1.ENTITLEMENT_VALUEKEY=ev1.ENTITLEMENT_VALUEKEY and et1.endpointkey=ep1.endpointkey and et1.ENTITLEMENTTYPEKEY=ev1.ENTITLEMENTTYPEKEY and rule.rulesetkey=r.rulesetkey UNION
select distinct ev2.ENTITLEMENT_VALUE from risks r,functions f2,function_entitlements fe2,entitlement_values ev2,entitlement_types et2,endpoints ep2,rulesets rule where r.function2key=f2.functionkey and f2.functionkey=fe2.functionkey and fe2.ENTITLEMENT_VALUEKEY=ev2.ENTITLEMENT_VALUEKEY and et2.ENTITLEMENTTYPEKEY=ev2.ENTITLEMENTTYPEKEY and et2.ENDPOINTKEY=ep2.ENDPOINTKEY and rule.rulesetkey=r.rulesetkey))