Analytics - Need a query to get the users with access to specific entitlement.

[Mukul]

Hi All,

I have a requirement to get the list of users with active ad accounts and access to specific entitlements. I used the below query and It seems to work, however, when I goto entitlement and accounts and export from there, the number doesnt match..

SELECT
u.username,
u.firstname,
u.lastname,
u.statuskey AS status_key,
accounts.name AS account_name,
endpoints.ENDPOINTNAME,
accounts.STATUS AS account_status,
et.displayname AS entitlement_type,
ev.entitlement_value,
ev.displayname AS entitlement_display_name,
ev.status AS entitlement_status,
m.username AS manager_username,
m.displayname AS manager_name,
accounts.customproperty1,
accounts.customproperty2,
accounts.customproperty30
FROM
users u
LEFT JOIN user_accounts ua ON ua.userkey = u.userkey
LEFT JOIN accounts ON accounts.accountkey = ua.accountkey
LEFT JOIN endpoints ON endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
LEFT JOIN account_entitlements1 ae ON ae.accountkey = accounts.ACCOUNTKEY
LEFT JOIN entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey
LEFT JOIN entitlement_types et ON et.entitlementtypekey = ev.entitlementtypekey
LEFT JOIN users m ON m.userkey = u.manager
WHERE
u.statuskey = 1
AND accounts.status != 'SUSPENDED FROM IMPORT SERVICE'
and ev.displayname='ENT_NAME'

[rushikeshvartak]

• Does entitlement is assigned to inactive & SFIS accounts ?

[Mukul]

No,

BTW : Does my query looks correct?

[rushikeshvartak]

Yes

[NM]

Hi @Mukul , you are pulling in manually suspended accounts as well in report.

Add a filter like you added for suspended from import service.