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 initiated per Employee type - BeSEE, DA Vendor, US Vendor

AshirvadhN
Regular Contributor
Regular Contributor

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.

5 REPLIES 5

DaanishJawed
Saviynt Employee
Saviynt Employee

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;

-

AshirvadhN
Regular Contributor
Regular Contributor

Hi @DaanishJawed 

Where i can add the condition employeetype in ('BeSEE' , 'DA Vendor' ,' US Vendor')

DixshantValecha
Saviynt Employee
Saviynt Employee

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.

Hi @DixshantValecha 

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

AshirvadhN_0-1679585245075.png

 

DixshantValecha
Saviynt Employee
Saviynt Employee

To achieve the specific tasks you mentioned, you can add additional columns to the SELECT statement based on the information available.