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

Analytics Query - Fetch the complete details for ARS Request

myadav
New Contributor
New Contributor

Hello Team,

We have a requirement to fetch the complete details for  ARS Request (description, business justification source table) for a particular request id.

Need to get description, business justification for a specific request id and update them via analytics

If someone has a query or a reference article please share.

Regards
Manoj Yadav
1 REPLY 1

PremMahadikar
All-Star
All-Star

Hi @myadav ,

Please find the below query: (Add or remove columns as required)

Select 
	`REQUEST ID` as 'Request ID', 
	`REQUEST TYPE` as 'Request Type', 
	`REQCOMMENTS` as 'Request Comments',
	`REQUEST SUBMIT DATE` as 'Request Submission Date', 
	`REQUESTED FOR` as 'Requested For', 
	`REQUESTEE NAME` as 'Request Name', 
	APPLICATION as 'Application', 
	`REQUESTED ENTITLEMENT` as 'Requested Entitlement',
	`ENTCOMMENTS` as 'Business Justification',
	SOD_EXCEPTION as 'SOD Exception',
	MITIGATINGCONTROL as 'Mitigating Control', 
	`RISK TYPE` as 'Risk Type', 
	`REQUESTED BY` as 'Requested By', 
	`APPROVAL TYPE` as 'Approval Type', 
	`ASSIGNEE ID` as 'Assignee ID', 
	`ASSIGNEE NAME` as 'Assignee Name', 
	case 
		when `RequestAccessStatus` = 3 then max(APPROVEDATE) 
		else null 
	end as `Approval Date`, 
	`REQUEST STATUS` as 'Request Status', 
	case 
		when `RequestAccessStatus` = 3 then max(APPROVEDATE) 
		else null 
	end `Request Completion Date`, 
	case 
		when `TASK STATUS` = 'Completed' then (taskupdatedate) 
		else null 
	end `Task Completion Date`, 
	`TASK STATUS` as 'Task Status' 
from 
	(SELECT 
		DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID', 
		CASE 
			WHEN (ar.requesttype = 1 or ar.requesttype = 3) THEN 'Grant Access' 
			WHEN ar.requesttype = 2 THEN 'Revoke Access' 
			WHEN ar.requesttype = 12 THEN 'Update Account' 
			WHEN ar.requesttype = 11 THEN 'Emergency Access Request' 
		END AS 'REQUEST TYPE', 
		ar.comments as 'REQCOMMENTS',
		ar.REQUESTDATE AS 'REQUEST SUBMIT DATE', 
		u2.username AS 'REQUESTED FOR', 
		CONCAT(u2.FIRSTNAME, ' ', u2.LASTNAME) AS 'REQUESTEE NAME', 
		ENDPOINTASCSV AS 'APPLICATION', 
		case 
			when ar.requesttype != 11 then IFNULL((SELECT entitlement_value FROM entitlement_values v WHERE v.entitlement_valuekey = ra.accesskey), 'Account') 
			else (select role_name from roles r where r.ROLEKEY = ra.ACCESSKEY) 
		end AS 'REQUESTED ENTITLEMENT', 
		ra.comments as 'ENTCOMMENTS',
		(select GROUP_CONCAT(distinct EXCEPTIONNAME SEPARATOR ',') from request_exceptions ex where ex.requestkey = ar.requestkey) as SOD_EXCEPTION, 
		(select GROUP_CONCAT(distinct c.MITIGATINGCONTROL SEPARATOR ',') from request_exceptions ex, mitigatingcontrols c where ex.requestkey = ar.requestkey and c.MITIGATINGCONTROLID = ex.MITIGATINGCONTROL) as MITIGATINGCONTROL, 
		(SELECT CASE WHEN v.RISK = 0 THEN 'None' WHEN v.RISK = 1 THEN 'Very Low' WHEN v.RISK = 2 THEN 'Low' WHEN v.RISK = 3 THEN 'Medium' WHEN v.RISK = 4 THEN 'High' WHEN v.RISK = 5 THEN 'Very High' END AS 'RISK TYPE' FROM entitlement_values v WHERE v.entitlement_valuekey = ra.accesskey) AS 'RISK TYPE', 
		(SELECT username FROM users t WHERE t.userkey = ar.requestor) AS 'REQUESTED BY', 
		aa.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE', 
		u.username AS 'ASSIGNEE ID', 
		CONCAT(u.FIRSTNAME, ' ', u.LASTNAME) AS 'ASSIGNEE NAME' , 
		CASE 
			WHEN aa.status = 1 THEN 'Pending Approval' 
			WHEN aa.STATUS = 2 THEN 'Approved' 
			WHEN aa.STATUS = 3 THEN 'Rejected' 
			WHEN aa.status = 4 THEN 'Escalated' 
			WHEN aa.STATUS = 6 THEN 'Discontinued' 
		END 'REQUEST STATUS', 
		ra.status as RequestAccessStatus, 
		aa.APPROVEDATE, 
		t2.updatedate as taskupdatedate, 
		CASE 
			WHEN t2.status = 1 THEN 'Open' 
			WHEN t2.status = 2 THEN 'InProcess' 
			WHEN t2.status = 3 THEN 'Completed' 
			WHEN t2.status = 4 THEN 'Discontinued' 
		END AS 'TASK STATUS' 
	FROM 
		ARS_REQUESTS ar left join request_access ra on ar.REQUESTKEY = ra.REQUESTKEY 
		left join ACCESS_APPROVERS aa on ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY
		left join USERS u on u.userkey = aa.APPROVEBY
		left join USERS u2 on  u2.userkey = ra.userkey
		 left join arstasks t2 on t2.requestaccesskey = ra.request_accesskey
		ORDER BY jbpmprocessinstanceid , JBPM_ACTIVITY_NAME , u.username
	) as t1 group by `REQUEST ID`, `REQUEST TYPE`,`REQCOMMENTS`, `REQUEST SUBMIT DATE`, `REQUESTED FOR`, `REQUESTEE NAME`, `REQUESTED ENTITLEMENT`,`ENTCOMMENTS`, SOD_EXCEPTION, MITIGATINGCONTROL, `RISK TYPE`, `REQUESTED BY`, `APPROVAL TYPE`, `ASSIGNEE ID`, `ASSIGNEE NAME`, `TASK STATUS`;

 

If this helps your question, please consider selecting Accept As Solution and hit Kudos