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'
