Query to have acces added or revoked

[esinam_g]

Hello 

I am trying to write a query that can list all the acces added/revoked from april to now

How can i do that please

The acces could be entitlements or roles

Thank you

[rushikeshvartak]

• SELECT taskkey, ev.entitlement_value
  FROM arstasks a,entitlement_Values ev
  WHERE a.tasktype IN (1, 2)
  AND a.status = 3 and ev.entitlement_Valuekey = a.entitlement_valuekey
  AND a.updatedate >= '2024-04-01'
  AND a.updatedate <= CURRENT_DATE;

Refer https://forums.saviynt.com/t5/tag/SQL_RequestHistory/tg-p

[esinam_g]

Hello thanks for theupdate

i tried to add the roles and the first and last name of the users also,but it s not working

• SELECT a.taskkey, ev.entitlement_value, r.role_name, u.first_name, u.last_name
  FROM arstasks a
  JOIN entitlement_Values ev ON ev.entitlement_Valuekey = a.entitlement_valuekey
  JOIN roles r ON r.rolekey = a.rolekey -- assuming arstasks contains rolekey
  JOIN users u ON u.userkey = a.userkey
  WHERE a.tasktype IN (1, 2)
  AND a.status = 3
  AND a.updatedate >= '2024-04-01'
  AND a.updatedate <= CURRENT_DATE;

[rushikeshvartak]

You need extract roles from separate query from role_user_accounts table

[esinam_g]

by using this query below i was able to extract all the added entitlements to users during a period of time.

SELECT u.USERNAME AS 'USERNAME', u.FIRSTNAME AS 'FIRST NAME', u.LASTNAME AS 'LAST NAME', ev.entitlement_value

FROM arstasks a

JOIN entitlement_Values ev ON ev.entitlement_Valuekey = a.entitlement_valuekey

JOIN users u ON u.userkey = a.userkey

WHERE a.tasktype IN (1, 2)

  AND a.status = 3

  AND a.updatedate >= '2024-04-01'

  AND a.updatedate <= CURRENT_DATE;

However how can i modify the query to extract the revoked acces?

[rushikeshvartak · Accepted solution]

SELECT DISTINCT u.username  AS 'USERNAME',
                u.firstname AS 'FIRST NAME',
                u.lastname  AS 'LAST NAME',
                r.role_name AS 'Access',
                'Role'      AS type
FROM   arstasks a
       JOIN roles r
         ON r.rolekey = a.assignedfromrole
       JOIN users u
         ON u.userkey = a.userkey
WHERE  a.tasktype IN ( 1, 2 )
       AND a.status = 3
       AND a.updatedate >= '2024-04-01'
       AND a.updatedate <= CURRENT_DATE
UNION
SELECT DISTINCT u.username           AS 'USERNAME',
                u.firstname          AS 'FIRST NAME',
                u.lastname           AS 'LAST NAME',
                ev.entitlement_value AS 'Access',
                'Entitlement'        AS type
FROM   arstasks a
       JOIN entitlement_values ev
         ON ev.entitlement_valuekey = a.entitlement_valuekey
       JOIN users u
         ON u.userkey = a.userkey
WHERE  a.tasktype IN ( 1, 2 )
       AND a.status = 3
       AND a.updatedate >= '2024-04-01'
       AND a.updatedate <= CURRENT_DATE;