03/14/2023 09:55 AM
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.
03/14/2023 01:22 PM - edited 03/14/2023 01:32 PM
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 -
04/02/2023 07:34 PM
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
03/30/2023 07:55 AM
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
04/02/2023 11:03 PM
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
04/03/2023 04:56 AM
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
04/25/2023 12:01 PM
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.