03/14/2023 09:46 AM
Hi Team,
Does anyone worked on this report.
we need to generate a report for the Access Requests initiated per Employee type - BeSEE, DA Vendor, US Vendor. By day/by week.
If anyone worked on this please help me.
Solved! Go to Solution.
03/15/2023 03:04 PM
Hi @AshirvadhN ,
Thanks for reaching out.
You can refer the below query where in it will give you the data for all the requests submitted along-with the user's employee types. You can tweak the query and add your condition based on the different employee types.
SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS 'REQUEST ID',
AR.ENDPOINTASCSV AS 'APPLICATION',
(SELECT
CONCAT(FIRSTNAME,
' ',
LASTNAME,
' (',
USERNAME,
')')
FROM
USERS
WHERE
RA.USERKEY = USERKEY) AS 'USER',
(SELECT
employeetype
FROM
USERS
WHERE
RA.USERKEY = USERKEY) AS 'Employee Type',
(SELECT
CONCAT(FIRSTNAME,
' ',
LASTNAME,
' (',
USERNAME,
')')
FROM
USERS
WHERE
AR.REQUESTOR = USERKEY) AS 'REQUESTOR',
AR.REQUESTDATE AS 'Request Date'
FROM
ARS_REQUESTS AR,
REQUEST_ACCESS RA,
ACCESS_APPROVERS AA
WHERE
AR.REQUESTKEY = RA.REQUESTKEY
AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY;
-
03/16/2023 08:49 AM
Hi @DaanishJawed
Where i can add the condition employeetype in ('BeSEE' , 'DA Vendor' ,' US Vendor')
03/22/2023 10:14 PM
Hi @AshirvadhN
To add the condition employeetype in ('BeSEE' , 'DA Vendor' ,' US Vendor'), you can modify the WHERE clause to include this condition as follows:
WHERE
AR.REQUESTKEY = RA.REQUESTKEY
AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
AND (SELECT employeetype FROM USERS WHERE RA.USERKEY = USERKEY) IN ('BeSEE', 'DA Vendor', 'US Vendor')
This will ensure that only rows where the employeetype value is one of the specified values ('BeSEE', 'DA Vendor', 'US Vendor') will be returned.
03/23/2023 08:27 AM
How to get specific tasks like Add access, remove access, new account, remove account, update account.
SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS 'REQUEST ID',
AR.ENDPOINTASCSV AS 'APPLICATION',
(SELECT
CONCAT(FIRSTNAME,
' ',
LASTNAME,
' (',
USERNAME,
')')
FROM
USERS
WHERE
RA.USERKEY = USERKEY) AS 'USER',
(SELECT
employeetype
FROM
USERS
WHERE
RA.USERKEY = USERKEY) AS 'Employee Type',
(SELECT
CONCAT(FIRSTNAME,
' ',
LASTNAME,
' (',
USERNAME,
')')
FROM
USERS
WHERE
AR.REQUESTOR = USERKEY) AS 'REQUESTOR',
AR.REQUESTDATE AS 'Request Date'
FROM
ARS_REQUESTS AR,
REQUEST_ACCESS RA,
ACCESS_APPROVERS AA
WHERE
AR.REQUESTKEY = RA.REQUESTKEY
AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
AND (SELECT employeetype FROM USERS WHERE RA.USERKEY = USERKEY) IN ('BeSEE', 'DA Vendor', 'US Vendor');
My output should be in this way
03/24/2023 12:46 AM
To achieve the specific tasks you mentioned, you can add additional columns to the SELECT statement based on the information available.