and more in a single search tool across platforms. Read the announcement here. |
01/14/2024 05:57 PM
Hi All,
I want to write a SQL query to find out below scenario :
1. All active users with access to saviynt.
Is it possible to achieve this if yes can you provide the SQL query for the same.
Thanks.
01/14/2024 06:05 PM - edited 01/14/2024 06:08 PM
SELECT EP.displayname AS APPLICATION,
ACC.NAME AS 'ACCOUNT NAME',
CASE
WHEN ACC.status IS NULL THEN ''
WHEN ACC.status = 1 THEN 'Active'
WHEN ACC.status = 2 THEN 'Inactive'
ELSE ACC.status
END 'ACCOUNT STATUS',
EV.entitlement_value AS 'ACCESS',
ET.displayname AS 'ACCESS TYPE',
U.username AS USERNAME,
U.firstname AS 'USER FIRSTNAME',
U.lastname AS 'USER LASTNAME',
U.email AS 'USER EMAIL',
U.employeetype AS 'USER TYPE',
CASE
WHEN U.statuskey IS NULL THEN 'Not Available'
WHEN U.statuskey = 0 THEN 'Inactive'
WHEN U.statuskey = 1 THEN 'Active'
ELSE U.statuskey
END 'USER STATUS'
FROM accounts ACC
LEFT JOIN account_entitlements1 AE1
ON ACC.accountkey = AE1.accountkey
LEFT JOIN entitlement_values EV
ON AE1.entitlement_valuekey = EV.entitlement_valuekey
LEFT JOIN entitlement_types ET
ON EV.entitlementtypekey = ET.entitlementtypekey
LEFT JOIN user_accounts UA
ON UA.accountkey = ACC.accountkey
LEFT JOIN users U
ON U.userkey = UA.userkey
LEFT JOIN endpoints EP
ON ACC.endpointkey = EP.endpointkey
LEFT JOIN securitysystems SS
ON EP.securitysystemkey = SS.systemkey
WHERE ACC.status IN ( '1', 'Manually Provisioned', 'Active' )