Saviynt Query Portal

All Modules

ARS Requests ARS Tasks Accounts Accounts, Endpoint Accounts, Endpoints Analytics Audit Logs Audit Trail Campaign Certification Connections Connections, Job Control Panel Endpoints Job Control Panel Roles Rules Security Systems User Group Users Users, Accounts Users, Accounts, Endpoint Users, Entitlements Users, Roles Users, SAV ROLES

Users having accounts in 3 Endpoints

Module: Users, Accounts, Endpoint

Description

Get all users who have account in particular endpoint, print necessary user and account details

Dynamic Input

Endpoint Name

Execute in

Data Analyzer, Analytics

SQL Query

SELECT
u.userkey,
u.username,
u.firstname,
u.lastname,
u.email,
CASE
WHEN u.statuskey = 0 THEN 'Inactive'
WHEN u.statuskey = 1 THEN 'Active'
ELSE 'Unknown'
END AS User_Status,
a.accountkey,
a.name AS ACCOUNTNAME,
a.displayname AS DISPLAYNAME,
CASE
WHEN a.status = 1 THEN 'Active'
WHEN a.status = 2 THEN 'Inactive'
WHEN a.status = 3 THEN 'Decommission Active'
WHEN a.status = 4 THEN 'Decommission Inactive'
WHEN a.status = 'Manually Provisioned' THEN 'Manually Provisioned'
WHEN a.status = 'Manually Suspended' THEN 'Manually Suspended'
WHEN a.status = 'SUSPENDED FROM IMPORT SERVICE' THEN 'Deleted'
ELSE a.status
END AS ACCOUNT_STATUS,
e.endpointname
FROM users u
JOIN user_accounts ua
ON u.userkey = ua.userkey
JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints e
ON a.endpointkey = e.endpointkey
WHERE e.endpointname IN (
'AWSl100',
'Privileged_Account',
'AWStrainingConnection'
)
AND u.userkey IN (
SELECT u2.userkey
FROM users u2
JOIN user_accounts ua2 ON u2.userkey = ua2.userkey
JOIN accounts a2 ON ua2.accountkey = a2.accountkey
JOIN endpoints e2 ON a2.endpointkey = e2.endpointkey
WHERE e2.endpointname IN (
'AWSl100',
'Privileged_Account',
'AWStrainingConnection'
)
GROUP BY u2.userkey
HAVING COUNT(DISTINCT e2.endpointname) = 3
)
ORDER BY u.firstname, e.endpointname;

Comments

nan

User and Account Details in Specific Endpoint