03/27/2023 08:05 AM
03/27/2023 10:11 AM - edited 03/27/2023 10:12 AM
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
03/27/2023 10:15 AM - edited 03/28/2023 01:56 AM
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
03/28/2023 04:48 AM
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;
03/28/2023 04:50 AM
Am getting this error.
03/30/2023 08:43 AM
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