Entitlement is assigned but role is not assigned

[Manu269]

Hello Team,

We need an assistance for a query where some of our users have already access to entitlements which are part of enterprise role but do not have access to the enterprise role.

Can someone share the query to fetch all such users which satisfy above condition?

It's for specific endpoint.

[rushikeshvartak]

SELECT CASE WHEN rva.MISMATCH_TYPE = 'ENDDATE_MISMATCH' THEN 'End Date Mismatch' WHEN rva.MISMATCH_TYPE = 'EXTRA' THEN 'Surplus Access' WHEN rva.MISMATCH_TYPE = 'MISSED' THEN 'Missing Access' ELSE rva.MISMATCH_TYPE END as 'Mismatch Type', CASE WHEN rva.MISMATCH_TYPE = 'ENDDATE_MISMATCH' THEN 'End Date Mismatch' WHEN rva.MISMATCH_TYPE = 'EXTRA' THEN 'Surplus Access' WHEN rva.MISMATCH_TYPE = 'MISSED' THEN 'Missing Access' ELSE rva.MISMATCH_TYPE END as mismatchType, CASE WHEN rva.REASON IN ('ANALYTICS_V2' , 'ANALYTICS') THEN 'Deprovisioned from Analytics' WHEN rva.REASON IN ('REQUEST', 'CERTIFICATION') THEN CONCAT('Deprovisioned from ', CONCAT(UCASE(LEFT(LCASE(rva.REASON), 1)), SUBSTRING(LCASE(rva.REASON), 2))) WHEN rva.REASON = 'WEBSERVICE' THEN 'Deprovisioned from API' WHEN rva.REASON = 'ZERODAY' THEN 'Deprovisioned from BirthRight' WHEN rva.REASON = 'SOD' THEN 'Deprovisioned from SOD' WHEN rva.REASON = 'PROVRULE' THEN 'Deprovisioned from Rule' WHEN rva.REASON = 'NOT_REQUESTABLE' THEN 'Non Requestable Entitlement Type' WHEN rva.REASON = 'INCOMPLETE_TASK' THEN 'Incomplete Task' WHEN rva.REASON = 'PROVISIONING_ERROR' THEN 'Provisioning Error' WHEN rva.REASON = 'OTHERS' THEN 'Others' WHEN rva.REASON = 'INACTIVE_ACCOUNTS' THEN 'Inactive Accounts' WHEN rva.REASON = 'UNKNOWN' THEN 'Unknown' WHEN rva.REASON = 'DEPROVISIONING_ERROR' THEN 'Deprovisioning Error' WHEN rva.REASON = 'ENTITLEMENT_NOT_PRESENT' THEN 'Entitlement is not Present in Role' WHEN rva.REASON = 'ENTITLEMENT_NOT_PRESENT_CHILD_ROLE' THEN 'Entitlement is not Present in Child Role' WHEN rva.REASON = 'INACTIVE_ROLE' THEN 'Inactive Role is Present With User' WHEN rva.REASON = 'INACTIVE_CHILD_ROLE' THEN 'Inactive Child Role is Present With User' WHEN rva.REASON = 'INACTIVE_USERS' THEN 'Inactive Users' WHEN rva.REASON = 'LOWER_ENTITLEMENT_END_DATE' THEN 'Lower Entitlement End Date' WHEN rva.REASON = 'HIGHER_ENTITLEMENT_END_DATE' THEN 'Higher Entitlement End Date' WHEN rva.REASON = 'ROLE_NOT_ASSIGNED' THEN 'Role not Assigned to User' WHEN rva.REASON = 'CHILD_ROLE_NOT_ASSIGNED' THEN 'Child Role not Assigned to User' ELSE 'Unknown' END AS 'Reason', CASE WHEN rva.REASON IN ('ANALYTICS_V2' , 'ANALYTICS') THEN 'Deprovisioned from Analytics' WHEN rva.REASON IN ('REQUEST', 'CERTIFICATION') THEN CONCAT('Deprovisioned from ', CONCAT(UCASE(LEFT(LCASE(rva.REASON), 1)), SUBSTRING(LCASE(rva.REASON), 2))) WHEN rva.REASON = 'WEBSERVICE' THEN 'Deprovisioned from API' WHEN rva.REASON = 'ZERODAY' THEN 'Deprovisioned from BirthRight' WHEN rva.REASON = 'SOD' THEN 'Deprovisioned from SOD' WHEN rva.REASON = 'PROVRULE' THEN 'Deprovisioned from Rule' WHEN rva.REASON = 'NOT_REQUESTABLE' THEN 'Non Requestable Entitlement Type' WHEN rva.REASON = 'INCOMPLETE_TASK' THEN 'Incomplete Task' WHEN rva.REASON = 'PROVISIONING_ERROR' THEN 'Provisioning Error' WHEN rva.REASON = 'OTHERS' THEN 'Others' WHEN rva.REASON = 'INACTIVE_ACCOUNTS' THEN 'Inactive Accounts' WHEN rva.REASON = 'UNKNOWN' THEN 'Unknown' WHEN rva.REASON = 'DEPROVISIONING_ERROR' THEN 'Deprovisioning Error' WHEN rva.REASON = 'ENTITLEMENT_NOT_PRESENT' THEN 'Entitlement is not Present in Role' WHEN rva.REASON = 'ENTITLEMENT_NOT_PRESENT_CHILD_ROLE' THEN 'Entitlement is not Present in Child Role' WHEN rva.REASON = 'INACTIVE_ROLE' THEN 'Inactive Role is Present With User' WHEN rva.REASON = 'INACTIVE_CHILD_ROLE' THEN 'Inactive Child Role is Present With User' WHEN rva.REASON = 'INACTIVE_USERS' THEN 'Inactive Users' WHEN rva.REASON = 'LOWER_ENTITLEMENT_END_DATE' THEN 'Lower Entitlement End Date' WHEN rva.REASON = 'HIGHER_ENTITLEMENT_END_DATE' THEN 'Higher Entitlement End Date' WHEN rva.REASON = 'ROLE_NOT_ASSIGNED' THEN 'Role not Assigned to User' WHEN rva.REASON = 'CHILD_ROLE_NOT_ASSIGNED' THEN 'Child Role not Assigned to User' ELSE 'Unknown' END AS mismatchSource, u.username as Username, u.FIRSTNAME as 'First Name', u.LASTNAME as 'Last Name', IFNULL(r.DISPLAYNAME, r.ROLE_NAME) as 'Role Name', rva.RUA_ENDDATE as 'Role End Date', IFNULL(cr.DISPLAYNAME, cr.ROLE_NAME) as 'Child Role', a.name as 'Account Name', et.entitlementname as 'Entitlement Type', ev.entitlement_value as 'Entitlement Value', rva.AE_ENDDATE as 'Entitlement End Date', rva.MISMATCH_SOURCEKEY as 'Reason Task Id', rva.entitlement_valuekey as entvaluekey, rva.rolekey as roleKey, rva.userkey as userKey, rva.accountkey as acctKey, rva.MISMATCH_SOURCEKEY as taskKey, rva. RUA_ENDDATE as ruaEndDate, rva.CHILDROLEKEY as childRoleKey, 'Align Role Access Mismatch' as Default_Action_For_Analytics FROM ROLEACCESSMISMATCHES rva LEFT JOIN users u ON rva.userkey=u.userkey INNER JOIN roles r ON rva.rolekey=r.rolekey LEFT JOIN roles cr ON rva.CHILDROLEKEY = cr.ROLEKEY INNER JOIN accounts a ON rva.accountkey=a.accountkey INNER JOIN entitlement_values ev ON rva.ENTITLEMENT_VALUEKEY=ev.entitlement_valuekey INNER JOIN entitlement_types et ON ev.entitlementtypekey=et.entitlementtypekey;

[Manu269]

This is not providing correct results 

[rushikeshvartak]

This is high-level sentence explain with use case what is not working and what is working. This is OOTB query and works well