01-10-2023 05:20 AM
I need to get those roles which dont have any entitlements tagged to it for particular security systems.
I tried to get data but it is showing no data found, I am able to get the roles listed under security systems,endpoints but not those roles which dont have any entitlements tagged to it .
Can anybody help in this query what need to be add.
select r.role_name,ep.endpointname from endpoints ep,entitlement_values ev,entitlement_types et,role_entitlements re,roles r,securitysystems ss where re.rolekey=r.rolekey and re.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY and ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and et.endpointkey=ep.endpointkey and ep.SECURITYSYSTEMKEY=(select ss.SYSTEMKEY from securitysystems where securitysystems.SYSTEMNAME ='endpointname') and ev.ENTITLEMENT_VALUEKEY NOT IN(select re.ENTITLEMENT_VALUEKEY from role_entitlements re where ev.ENTITLEMENT_VALUEKEY=re.ENTITLEMENT_VALUEKEY)
Thanks in Advance
01-10-2023 05:35 AM
select r.role_name,endpointkey from roles r where rolekey not in (select rolekey from role_entitlements re where re.rolekey=r.rolekey)