Click HERE to see how Saviynt Intelligence is transforming the industry. |
09/23/2024 03:09 AM - edited 09/23/2024 05:39 AM
Hi Team,
We have to create analytics to find
Users having Manager, Manager having AD account(not users account) show as Yes else NO and Manager's employeeid.
for a particular endpoint(endpointname="XYZ")
Required columns: username, user employeeid, manager, manager's employeeid, manager AD account in Yes or no.
09/23/2024 09:05 AM
Share your draft query
09/23/2024 09:19 AM
Hi @rushikeshvartak ,
can you check this query. additionally, we have to show manager has AD account or not both.
SELECT
u.username AS "User Name",
u.employeeId AS "User Employee ID",
u.manager AS "Manager Id",
u.owner AS "Manager Name",
u2.employeeid AS "Manager Employee ID",
e.endpointname AS "Endpoint",
CASE u.statuskey WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' ELSE 'UNKNOWN' END AS "USER STATUS",
CASE a.status WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' ELSE 'UNKNOWN' END AS "USER AD Account Status",
CASE u2.statuskey WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' ELSE 'UNKNOWN' END AS "Manager STATUS",
CASE a2.status WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' ELSE 'UNKNOWN' END AS "Manager AD Account Status"
FROM
users u
LEFT JOIN users u2
ON u.manager = u2.userkey
join user_accounts ua on u.userkey = ua.userkey
join accounts a on a.accountkey = ua.accountkey
join endpoints e on e.endpointkey = a.endpointkey
LEFT JOIN user_accounts ua2 on u2.userkey = ua2.userkey
LEFT JOIN accounts a2 on a2.accountkey = ua2.accountkey
WHERE
u.manager is not null
AND a.status NOT IN (
'0',
'INACTIVE',
'Manually Suspended',
'SUSPENDED FROM IMPORT SERVICE' )
AND a2.status NOT IN (
'0',
'INACTIVE',
'Manually Suspended',
'SUSPENDED FROM IMPORT SERVICE' ) and
e.endpointname = 'XYZ'
09/23/2024 09:46 AM
SELECT u.username AS "User Name",
u.employeeid AS "User Employee ID",
u.manager AS "Manager Id",
u2.username AS "Manager Name",
u2.employeeid AS "Manager Employee ID",
e.endpointname AS "Endpoint",
CASE u.statuskey
WHEN '0' THEN 'INACTIVE'
WHEN '1' THEN 'ACTIVE'
ELSE 'UNKNOWN'
END AS "User Status",
CASE a.status
WHEN '0' THEN 'INACTIVE'
WHEN '1' THEN 'ACTIVE'
ELSE 'UNKNOWN'
END AS "User AD Account Status",
CASE u2.statuskey
WHEN '0' THEN 'INACTIVE'
WHEN '1' THEN 'ACTIVE'
ELSE 'UNKNOWN'
END AS "Manager Status",
CASE a2.status
WHEN '0' THEN 'INACTIVE'
WHEN '1' THEN 'ACTIVE'
ELSE 'UNKNOWN'
END AS "Manager AD Account Status",
CASE
WHEN ua2.userkey IS NOT NULL
AND ua2.userkey != '' THEN 'Yes'
ELSE 'No'
END AS "Manager AD Account Exists"
FROM users u
LEFT JOIN users u2
ON u.manager = u2.userkey
JOIN user_accounts ua
ON u.userkey = ua.userkey
JOIN accounts a
ON a.accountkey = ua.accountkey
JOIN endpoints e
ON e.endpointkey = a.endpointkey
LEFT JOIN user_accounts ua2
ON u2.userkey = ua2.userkey
LEFT JOIN accounts a2
ON a2.accountkey = ua2.accountkey
WHERE u.manager IS NOT NULL
AND a.status NOT IN ( '0', 'INACTIVE', 'Manually Suspended',
'SUSPENDED FROM IMPORT SERVICE' )
AND ( a2.status IS NULL
OR a2.status NOT IN ( '0', 'INACTIVE', 'Manually Suspended',
'SUSPENDED FROM IMPORT SERVICE' ) )
AND e.endpointname = 'XYZ';
09/23/2024 09:10 AM
SELECT u.username AS user_username,
u.employeeid AS user_employeeid,
m.username AS manager_username,
m.employeeid AS manager_employeeid,
CASE
WHEN ua.userkey IS NOT NULL
AND ua.userkey != '' THEN 'Yes'
ELSE 'No'
END AS manager_ad_account
FROM users u
JOIN users m
ON u.manager = m.userkey
LEFT JOIN user_accounts ua
ON m.userkey = ua.userkey
LEFT JOIN accounts a
ON ua.accountkey = a.accountkey
JOIN endpoints e
ON a.endpointkey = e.endpointkey
WHERE e.endpointname = 'Rushi AD';