11-17-2022 11:56 PM
We have a use case to build Analytics Report having New Account Task details. These tasks have dynamic attributes populated by the user when they request for new account.
How can we build our SQL queries to pull these dynamic attributes to be shown in the analytics report?
Below are the dyanmic attributes we have on endpoint level and on task created.
The query we are using now-> "Select taskkey as 'TASKID' , (select username from users where userkey = ars.userkey) as UserEmployeeID,
(select displayname from users where userkey = ars.userkey) as UserDisplayName,
(select title from users where userkey = ars.userkey) as UserJobTitle,
(select email from users where userkey = ars.userkey) as UserEmail,
(select u1.username from users u1 where userkey=(select manager from users where userkey = ars.userkey)) as ManagerEmplyeeID,
(select u1.displayname from users u1 where userkey=(select manager from users where userkey = ars.userkey)) as ManagerDisplayName,
(select entitlement_value from entitlement_values where ars.ENTITLEMENT_VALUEKEY=ENTITLEMENT_VALUEKEY)As EntitlementValue,TASKDATE as Creation_Date,UPDATEDATE as LastUpdateDate,(select endpointname from endpoints where endpointkey = ars.endpoint) as ENDPOINT, (SELECT SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID,'.',-1) from ARS_REQUESTS AR where AR.requestkey=ars.requestkey) AS 'REQUEST ID' ,case when tasktype = 1 then "Add Access" when tasktype = 12 then "Update Account" when tasktype = 2 then "Remove Access" when tasktype = 8 then "Remove Account" when tasktype = 3 then "NEW ACCOUNT" END Operation,AccountName, CASE when status = 1 then 'NEW' when status = 2 then 'IN PROGRESS' when status = 3 then 'COMPLETE' when status = 4 then 'DISCONTINUED' when status = 5 then 'PENDING CREATE' when status = 6 then 'PENDING PROVISIONING' when status = 7 then 'PROVISIONING FAILED' when status = 8 then 'ERROR' when status = 9 then 'NO ACTION REQUIRED' else 'CLOSED' end TASKSTATUS
from arstasks ars where status in ('1','2') and tasktype in ('1','2','3','8') and
endpoint in (select endpointkey from endpoints where endpointname in ('ABC'))"
Solved! Go to Solution.
11-18-2022 03:43 AM
The values selected in dynamic attributes in the access requests are stored in the request_access_attrs table. The arstasks table has the reference to the request_access table. You may join arstasks with request_access and then with request_access_attrs to get the relevant values.
select raa.ATTRIBUTE_NAME, raa.ATTRIBUTELABLE , raa.ATTRIBUTE_VALUE from arstasks a join request_access ra on a.REQUESTACCESSKEY =ra.REQUEST_ACCESSKEY join request_access_attrs raa on ra.REQUEST_ACCESSKEY =raa.REQUEST_ACCESS_KEY and raa.ATTRIBUTE_NAME not in ('ENDPOINT', 'ACCOUNTKEY','ENT_BY_RULE_ADD','ACCOUNTNAME');