Hello, I'm attempting to find any entitlements associated to endpoints in IGA that do not have an entitlements mapping record associated to them.
Essentially meaning the entitlements do not have an active directory group mapped to them to be granted. In the entitlementmap table, this would correlate to entitlement_value2key I believe.
When you look at the entitlement record on the entitlement_values table, you can see where it has an associated record in the entitlementmap table. When you attempt to query that in Analytics, I cannot find a way to duplicate that logic and check for any IS NULL values for entitlementmaps in the "entitlementmappingjson" field on the entitlement_value table.
When looking at the entitlementmap table, records only seem to be created here if the entitlement associated to an entitytlement type on the endpoint has an AD group mapped to it already. It doesn't appear to create any records on this table when the entitlement has nothing mapped to it / has a null entitlement_value2key.
In my query, I'm joining a few extra tables to provide more information so we can see the full picture of where they are tied to.
Query:
SELECT s.displayname AS 'Security System', ep1.endpointname AS 'Endpoint Name', ev1.entitlement_value AS 'ENTITLEMENT NAME', etype.entitlementname AS 'ENTITLEMENT TYPE'
FROM entitlement_values ev1
JOIN entitlement_types etype ON etype.entitlementtypekey = ev1.entitlementtypekey
JOIN endpoints ep1 ON ep1.endpointkey = etype.endpointkey and ep1.securitysystemkey='9'
JOIN securitysystems s ON s.systemkey = ep1.securitysystemkey
WHERE ev1.entitlementmappingjson IS NULL
