Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Export Report-All Requests completed within 7

Puspanjali
New Contributor
New Contributor

 

Hi Team,

I'm trying to export  all requests completed within 7 days

I'm using the below query but it returning a zero record

Can  team help me with this

 

SELECT DISTINCT
u2.USERNAME as 'REQUESTED FOR', u2.DISPLAYNAME, u2.JOBDESCRIPTION as "JOB TITLE", u2.DEPARTMENTNAME as "DEPARTMENT", u2.EMPLOYEETYPE as "TYPE OF EMPLOYEMENT", u2.CUSTOMPROPERTY6 as "LINE MANAGER EMAIL", SUBSTRING_INDEX(ars_requests.JBPMPROCESSINSTANCEID, '.', - 1) AS 'REQUEST_ID', ars_requests.REQUESTDATE, CASE WHEN ars_requests.REQUESTTYPE = '1' THEN 'ADD'
WHEN ars_requests.REQUESTTYPE = '3' THEN 'NEWACCOUNT'
WHEN ars_requests.REQUESTTYPE = '12' THEN 'UPDATEACCOUNT' WHEN ars_requests.REQUESTTYPE = '2' THEN 'DELETE' END AS 'REQUEST_TYPE', CASE ra.ACCESSTYPE WHEN '3' THEN 'NEW_ACCOUNT_REQUEST' WHEN '2' THEN 'ENTITLEMENT_VALUE'WHEN '1' THEN 'ROLE' END AS ACCESS_TYPE, group_concat((SELECT entitlement_value FROM entitlement_values WHERE entitlement_values.ENTITLEMENT_VALUEKEY = ra.ACCESSKEY))'ENTITLEMENT_VALUE'
FROM request_access ra, ars_requests, users u2
WHERE
ra.REQUESTKEY = ars_requests.REQUESTKEY
AND ra.STATUS IN (2 , 3)
AND ars_requests.ENDPOINTASCSV LIKE 'Oracle_ERP_TH'
AND ars_requests.COMPLETEDATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND ra.userkey= u2.userkey
and ra.ACCESSTYPE not in (3)
group by SUBSTRING_INDEX(ars_requests.JBPMPROCESSINSTANCEID, '.', - 1);

 

5 REPLIES 5

sundas7
Regular Contributor II
Regular Contributor II

Hi ,

 

How about this..Just tested in one of my DEV env.It seem to pull the data.

 

SELECT DISTINCT
u2.USERNAME as 'REQUESTED FOR', u2.DISPLAYNAME, u2.JOBDESCRIPTION as "JOB TITLE", u2.DEPARTMENTNAME as "DEPARTMENT", u2.EMPLOYEETYPE as "TYPE OF EMPLOYEMENT", u2.CUSTOMPROPERTY6 as "LINE MANAGER EMAIL", SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') ) AS 'REQUEST_ID', ar.REQUESTDATE, CASE WHEN ar.REQUESTTYPE = '1' THEN 'ADD'
WHEN ar.REQUESTTYPE = '3' THEN 'NEWACCOUNT'
WHEN ar.REQUESTTYPE = '12' THEN 'UPDATEACCOUNT' WHEN ar.REQUESTTYPE = '2' THEN 'DELETE' END AS 'REQUEST_TYPE', CASE ra.ACCESSTYPE WHEN '3' THEN 'NEW_ACCOUNT_REQUEST' WHEN '2' THEN 'ENTITLEMENT_VALUE'WHEN '1' THEN 'ROLE' END AS ACCESS_TYPE, group_concat((SELECT entitlement_value FROM entitlement_values WHERE entitlement_values.ENTITLEMENT_VALUEKEY = ra.ACCESSKEY))'ENTITLEMENT_VALUE'
FROM request_access ra, ars_requests ar, users u2
WHERE
ra.REQUESTKEY = ar.REQUESTKEY
AND ra.STATUS IN (2 , 3)
AND ar.ENDPOINTASCSV LIKE 'Oracle_ERP_TH'
AND ar.REQUESTDATE> DATE_SUB(NOW(),INTERVAL 7 DAY)
AND ra.userkey= u2.userkey
and ra.ACCESSTYPE not in (3)
ORDER BY jbpmprocessinstanceid;

 

Thanks

Shyam

Hi Shyam

Initially, I tested with ar.REQUESTDATE , but the issue with this is, suppose a user has submitted the request 20 days back and his request was approved today by the manager, then that user will not come in my report.

My expectation is all requests, were completed within an interval of 7 days irrespective of the request submitted date. then request and user details should be present in my report.

 

 

Join with access_approvers table to get max date i.e. approval date


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

Hi Rushikesh

Our environment's Current version  is SP3.12.14

I'm not sure, if it is the version issue or not but this table(access_approvers) is not present in the data analyzer's Schema table from UI

Puspanjali_1-1668328063405.png

And about this table(ars_requests),This column(COMPLETEDATE) is full blank

 

Puspanjali_2-1668328319808.pngPuspanjali_3-1668328366448.png

 

 

 

 

 

 

 

Completedate under ars_request won’t populate.

access_approvers table might not exposed in data analyzer 

refer tables 

https://saviynt.freshdesk.com/support/solutions/articles/43000521404-saviynt-enterprise-identity-clo...


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