Analytic to check password resets done by help desk

[aundreb]

Hello,

I'm working on an analytic to check the password resets done by help desk users via Saviynt ARS.

I have the below query that pulls the change password actions. How can I add a condition to this to only log data for users that have the SAV role ROLE_HELP_DESK or some sort of User group maybe?

select ua.TYPEOFACCESS as 'Object Type',ua.ObjectKey as 'Requestee PIN',ua.ActionType as 'Action Taken',u.username as 'Requester PIN', ua.IPADDRESS as 'IP Address',ua.ACCESSTIME as 'Start Time',ua.ACCESSTIME as 'Completed Time',ua.DETAIL as 'Message' from users u , userlogin_access ua, userlogins l where l.loginkey = ua.LOGINKEY and l.USERKEY = u.userkey and ua.AccessTime >= (NOW() - INTERVAL 4 DAY) and ua.Detail is not NULL and LOWER(ua.ActionType) ='change password'

[rushikeshvartak · Accepted solution]

SELECT ua.typeofaccess AS 'Object Type',
       ua.objectkey    AS 'Requestee PIN',
       ua.actiontype   AS 'Action Taken',
       u.username      AS 'Requester PIN',
       ua.ipaddress    AS 'IP Address',
       ua.accesstime   AS 'Start Time',
       ua.accesstime   AS 'Completed Time',
       ua.detail       AS 'Message'
FROM   users u
       join userlogins l
         ON l.userkey = u.userkey
       join userlogin_access ua
         ON l.loginkey = ua.loginkey
       left join user_savroles us
              ON us.userkey = u.userkey
       left join savroles s
              ON us.rolekey = s.rolekey
WHERE  ua.accesstime >= ( Now() - interval 4 day )
       AND ua.detail IS NOT NULL
       AND Lower(ua.actiontype) = 'change password'
       AND s.rolename = 'ROLE_HELP_DESK'; 

[aundreb]

That worked, thanks Rushikesh!