We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Volume of Access Requests completed

AshirvadhN
Regular Contributor
Regular Contributor

Hi Team,

Any of you worked on this report.

Please let me also know.

5 REPLIES 5

nimitdave
Saviynt Employee
Saviynt Employee

Below query will help you for reference. This will give all requests in last month

SELECT distinct SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID,'.',-1) AS 'REQUEST ID' , AR.ENDPOINTASCSV AS 'APPLICATION',
(SELECT concat(FIRSTNAME,' ',LASTNAME,' (',USERNAME,')') FROM USERS WHERE AR.REQUESTOR=USERKEY) AS 'REQUESTOR',AR.REQUESTDATE as 'Request Date',
CASE WHEN AR.STATUS=3 THEN 'Completed'
WHEN AR.STATUS=6 THEN 'Discontinued'
WHEN AR.STATUS=1 THEN 'Open'
WHEN AR.STATUS=2 THEN 'In Process'
WHEN AR.STATUS=4 THEN 'Expired'
WHEN AR.STATUS=5 THEN 'Approved'
END AS 'REQUEST STATUS'
FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA
WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
AND month(ar.requestdate) = month(now())-1;

 

You can add groupby clause to get the count on the basis of application,request type etc

AshirvadhN
Regular Contributor
Regular Contributor

Hi @nimitdave 

Thanks for sharing the query.

The Add access , remove access, new account, update user, update account tasks i want to fetch which are in completed based on the employeetype.

Any idea please

Hi Team,

Can someone see this query. 

For all employeetypes am try to fetch the completed tasks.

SELECT RA.requesttype,
CASE
WHEN RA.requesttype = 1 THEN 'Add Access'
WHEN RA.requesttype = 2 THEN 'Revoke Access'
WHEN RA.requesttype = 12 THEN 'Update Account'
END AS 'REQUEST TYPE'
COUNT(*)
FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA,USERS U
WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = AR.REQUEST_ACCESSKEY and U.USERKEY=AR.REQUESTKEY and U.USERKEY=RA.USERKEY and RA.STATUS = 5 and u.employeetype in ('BeSEE') and u.customproperty63 in ('BO',RO') group by u.employeetype,ra.requesttype
UNION all
SELECT RA.requesttype,
CASE
WHEN RA.requesttype = 1 THEN 'Add Access'
WHEN RA.requesttype = 2 THEN 'Revoke Access'
WHEN RA.requesttype = 12 THEN 'Update Account'
END AS 'REQUEST TYPE'
COUNT(*)
FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA,USERS U
WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = AR.REQUEST_ACCESSKEY and U.USERKEY=AR.REQUESTKEY and U.USERKEY=RA.USERKEY and RA.STATUS = 5 and u.employeetype in ('US Vendor') group by u.employeetype,ra.requesttype
union all
SELECT RA.requesttype,
CASE
WHEN RA.requesttype = 1 THEN 'Add Access'
WHEN RA.requesttype = 2 THEN 'Revoke Access'
WHEN RA.requesttype = 12 THEN 'Update Account'
END AS 'REQUEST TYPE'
COUNT(*)
FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA,USERS U
WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = AR.REQUEST_ACCESSKEY and U.USERKEY=AR.REQUESTKEY AND U.USERKEY=RA.USERKEY and RA.STATUS = 5 and u.employeetype in ('DA Vendor') group by u.employeetype,ra.requesttype;

AshirvadhN
Regular Contributor
Regular Contributor

Am getting this error.

AshirvadhN_0-1680004204086.png

 

Hi Team,

Can someone this query.

what is the condition i can use to fecth the access requests which are completed in previous 7 days.

SELECT
u.employeetype,
case
when at.tasktype = '1' then 'Add Access'
when at.tasktype = '2' then 'Remove Access'
when at.tasktype = '3' then 'New Account'
when at.tasktype = '12' then 'Update Account'
end as task_type,
count(at.tasktype) as task_count
FROM ARSTASKS at, users u
where at.userkey=u.userkey
and u.employeetype = 'DA Vendor'
group by at.tasktype
union
SELECT
u.employeetype,
case
when at.tasktype = '1' then 'Add Access'
when at.tasktype = '2' then 'Remove Access'
when at.tasktype = '3' then 'New Account'
when at.tasktype = '12' then 'Update Account'
end as task_type,
count(at.tasktype) as task_count
FROM ARSTASKS at, users u
where at.userkey=u.userkey
and u.employeetype = 'BeSEE' and u.customproperty63 in ('BO','RO')
group by at.tasktype
union
SELECT
u.employeetype,
case
when at.tasktype = '1' then 'Add Access'
when at.tasktype = '2' then 'Remove Access'
when at.tasktype = '3' then 'New Account'
when at.tasktype = '12' then 'Update Account'
end as task_type,
count(at.tasktype) as task_count
FROM ARSTASKS at, users u
where at.userkey=u.userkey
and u.employeetype = 'US Vendor'
group by at.tasktype