User-Entitlement Report

[Mahak_Acharya]

Hi,

We have an audit requirement to report users/accounts and their associated entitlements per application. For applications like AD where the user may have large number of groups associated to them, we are using JSON_ARRAYAGG function. This is erroring out due to the "max_bytes_length_exceeded_exception" which prevents the large number of data being retrieved in a single field.

Is there any other way to bring in this information in Saviynt reports.

Note: Group_concat() function is also trimming the entitlement data and hence not feasible.

[rushikeshvartak]

You can fetch report directly from database, if data is getting trim try to split data in multiple column,

Please share current version of sql

[Mahak_Acharya]

Hi Rushikesh,

We are generating the analytics report directly based on the database tables.

Could you please explain more on how we can split the data into multiple columns, as our requirement includes to show the user access in a single report and spliting the entitlements based on character length or any particular string would not be possible as we do not have a specific string to split on.

Is there any other way this can be achieved?

Regards,

Mahak

[rushikeshvartak]

Please share current query prepared

[Mahak_Acharya]

select distinct u.username as 'IAU Code', u.email as 'User Email', a.name as 'Account Name', case when a.status in ('Active','1','Manually Provisioned') then 'Active' else 'Inactive' end as 'Account Status', case when u.statuskey=0 then 'Inactive' when u.statuskey=1 then 'Active' end as 'User Status',date(u.enddate) as 'User Termination', date(a.LASTLOGONDATE) as 'Account Last Logon', case when DATEDIFF(now(),a.LASTLOGONDATE) > 180 then 'Dormant' else 'N/A' end as 'Dormant Account Check', az.AzureADLastLogOn as 'Azure AD Last LogOn', e.endpointname as 'Application Name', JSON_ARRAYAGG(ev.ENTITLEMENT_VALUE) as 'Entitlement Name', group_concat(substring_index(ar.jbpmprocessinstanceid,'.' ,-1)) as 'Request ID',
case when date(u.enddate)>date(a.LASTLOGONDATE) then 'No' when date(u.enddate)<date(a.LASTLOGONDATE) then 'Yes' else 'N/A' end as 'User logged in to App post termination'
from accounts a
join account_entitlements1 ae on a.accountkey=ae.accountkey
join user_accounts ua on ua.accountkey=a.accountkey
join users u on u.userkey=ua.userkey
join endpoints e on a.endpointkey=e.endpointkey
join entitlement_values ev on ev.ENTITLEMENT_VALUEKEY=ae.ENTITLEMENT_VALUEKEY
left join arstasks at on a.accountkey=at.accountkey
left join ars_requests ar on at.REQUESTKEY=ar.REQUESTKEY
inner join (select date(ad.customproperty34) as 'AzureADLastLogOn',ud.email as 'AzureUEmail' from accounts ad join user_accounts uad on uad.accountkey=ad.accountkey
join users ud on ud.userkey=uad.userkey where ad.endpointkey=3) az on az.AzureUEmail=u.email
where e.endpointkey=10 group by a.name,e.endpointname

[rushikeshvartak]

You should display data in separate row instead of same column. as per excel limit column length cant be extended