How to fetch user data who raised access or approved in last 6 months

[nitishdas]

Hi Team,

I would require help if I can query any tables to fetch the data who raised the request in Saviynt or has approved in last 6 months.

[rushikeshvartak]

Select `REQUEST ID` as 'Request ID', `REQUEST TYPE` as 'Request Type', `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', 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.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', (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 , request_access ra, ACCESS_APPROVERS aa, USERS u, USERS u2, arstasks t2 WHERE ar.REQUESTKEY = ra.REQUESTKEY AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY AND u.userkey = aa.APPROVEBY AND u2.userkey = ra.userkey AND t2.requestaccesskey = ra.request_accesskey and t2.ASSIGNEDFROMRule is null  ORDER BY jbpmprocessinstanceid , JBPM_ACTIVITY_NAME , u.username) as t1 group by `REQUEST ID`, `REQUEST TYPE`, `REQUEST SUBMIT DATE`, `REQUESTED FOR`, `REQUESTEE NAME`, `REQUESTED ENTITLEMENT`, SOD_EXCEPTION, MITIGATINGCONTROL, `RISK TYPE`, `REQUESTED BY`, `APPROVAL TYPE`, `ASSIGNEE ID`, `ASSIGNEE NAME`, `TASK STATUS`;