We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Report showing the entitlements most frequently requested

AshirvadhN
Regular Contributor
Regular Contributor

Hi Team,

Any of you worked on this type of report.

We need to generate a report for the entitlements which are most frequently requested.

6 REPLIES 6

DaanishJawed
Saviynt Employee
Saviynt Employee

Hi @AshirvadhN,

You can use the following tables to create a report.

arstasks

entitlement_values

endpoints

securitysystems

Sample Query below will give you the list of entitlements for which tasks are created. You can modify the query based on your requirement.

 

select at.entitlement_valuekey,ev.entitlement_value, ep.endpointname,ss.SYSTEMNAME, count(ev.entitlement_value) as 'COUNT' from arstasks at, endpoints ep, entitlement_values ev, securitysystems ss where ev.entitlement_valuekey=at.entitlement_valuekey and at.endpoint=ep.endpointkey and at.entitlement_valuekey=ev.entitlement_valuekey and at.securitysystem=ss.SYSTEMKEY  group by ev.entitlement_valuekey

 

You can join the above tables and create your query using the columns mentioned in the below document.

Refer the below Database schema -

https://docs.saviyntcloud.com/bundle/SSM-DB-Schema-Reference-v55x/page/Content/Identity-Repository-S...

Siva
Regular Contributor
Regular Contributor

Hi @DaanishJawed 

Is there any other to get most frequently entitlements fromARS_REQUESTS or from REQUEST_ACCESS.
Could you please suggest best approach ?

Thanks
Siva Avula

Siva
Regular Contributor
Regular Contributor

Hi @DaanishJawed 
Could you suggest any alternate for above mentioned query. because if i need to check same report from request histroy instead of ARS tasks, 
how can i join the endpoint and request histroy and entitiement_values tables.

Thanks
Siva Avula

use below tables 

ars_requests - requestkey (column for join)

request_access - requestkey,accesskey (column for join)

entitlement_Values -entitlement_valuekey (column for joins with request_access table),entitlementtypekey

entitlement_types - entitlementtypekey,endpointkey

endpoints - endpointkey

 

bold are column name for joins


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Siva
Regular Contributor
Regular Contributor

Hi Rushikesh,
Thanks for the response,from your inputs, I have made below query to fetch most frequentlyrequested entitlements,
Here I need to fetch request id and ent value and count of ent value no.of times its requested.

SELECT SUBSTRING_INDEX(ar.jbpmprocessinstanceid, '.', - 1) AS 'Request_ID',ev.entitlement_value,ep.endpointname,COUNT(ev.entitlement_value) AS 'COUNT'
FROM
ars_requests ar,
request_access ra,
entitlement_Values ev,
entitlement_types et,
endpoints EP
WHERE
ar.requestkey = ra.requestkey AND ev.entitlement_Valuekey = ra.ACCESSKEY AND et.ENDPOINTKEY = ep.ENDPOINTKEY and ar.requesttype='1'
GROUP BY ev.entitlement_value

The query resulting to huge count 3000, but in request history we dont have that many request at all.
Could you please look into this once and let me know where I am missing?

Thanks
Siva Avula

Hi @Siva ,

Since request ID and entitlement value key are both unique fields, it will not be possible to print both in separate columns. I have modified the query to include all the request ID's in one row for a specific entitlement using the group_concat function.

Below is the modified query. Kindly validate the same and see if this works -

SELECT 
  group_concat(SUBSTRING_INDEX(ar.jbpmprocessinstanceid, '.', - 1)) AS 'Request_ID',
    ev.entitlement_value,
    ep.endpointname,
    COUNT(ev.entitlement_valuekey) AS 'COUNT'
FROM
    ars_requests ar,
    request_access ra,
    entitlement_Values ev,
    entitlement_types et,
    endpoints EP
WHERE
    ar.requestkey = ra.requestkey
        AND ev.entitlement_Valuekey = ra.ACCESSKEY
        AND et.ENDPOINTKEY = ep.ENDPOINTKEY
        AND ev.ENTITLEMENTTYPEKEY= et.ENTITLEMENTTYPEKEY
        AND ar.requesttype = '1'
GROUP BY ev.entitlement_valuekey;

 Thanks.