Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

How to create a report that shows multiple users in a single Entitlement row

JohnDoe
Regular Contributor
Regular Contributor

Hello everyone,

 

I would like to create an Entitlement-based report with the following criteria:

Criteria
・SecuritySystem : Azure AD
・Endpoint : Azure AD
・Entitlement Type : AADGroup
・Entitlement Customproperty8: True
・If the Entitlement is updated in any way

Parameters you want to print
・Entitlement Value
・Customproperty7 in Entitlement
・User with the Entitlement

I think it is important to show multiple users in a single Entitlement line.
Could you tell me the SQL query to create such a report?
The photo is the image of the report I expect to complete.

JohnDoe_0-1691653282471.png

 

1 REPLY 1

armaanzahir
Valued Contributor
Valued Contributor

Hi @JohnDoe ,

 

Can you try the below query:

select ev.ENTITLEMENT_VALUE as 'Entitlement Value' ,ev.customproperty7 as 'Entitlement Custom Property7', Group_concat(u.username) as 'Users that are part of the Entitlement' from entitlement_values ev left join entitlement_types et on ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY left join account_entitlements1 ae on ev.ENTITLEMENT_VALUEKEY=ae.ENTITLEMENT_VALUEKEY left join user_accounts ua on ae.accountkey=ua.accountkey left join users u on ua.userkey=u.userkey where ev.ENTITLEMENTTYPEKEY=11 and et.endpointkey=3 group by ev.ENTITLEMENT_VALUE ORDER by ev.ENTITLEMENT_VALUE ASC limit 100

You may add extra conditions to this query and replace the endpointkey and entitlementtypekey in the above query, but this query would help you to concatenate all users belonging to a particular Entitlement. 

 

Regards,
Md Armaan Zahir