Click HERE to see how Saviynt Intelligence is transforming the industry. |
08/02/2024 07:40 AM
Hi team, I have to write a query for analytics reports to find Users having AD UPN and Empty value in Saviynt.
the AD UPN is stored in customproperty2 and in Saviynt it was email from users table.
we have to find that users from only AD
Solved! Go to Solution.
08/02/2024 08:03 AM
Hello @Pratham ,
You can use following relation between the tables and compare your CP2 from AD and email from users,
user_accounts ua ON u.userkey = ua.userkey
accounts a ON a.accountkey = ua.accountkey
Thanks,
Rahul
Please accept this as solution if it resolves your issue.
08/02/2024 10:55 AM
Hi @rahul_p
Thanks for the reply.
Can you please check this Query:
SELECT
U.SYSTEMUSERNAME AS 'SAV_USERNAME',
U.FIRSTNAME, U.LASTNAME,
A.CUSTOMPROPERTY2 AS 'AD_UPN',
U.EMAIL as 'SAV_UPN',
E.ENDPOINTNAME AS 'Endpoint',
U.departmentname as 'DEPARTMENT',
E.endpointKey as 'Enp1'
FROM USERS U, ACCOUNTS A, USER_ACCOUNTS UA,ENDPOINTS E, SECURITYSYSTEMS S
WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY AND
U.USERKEY = UA.USERKEY AND
A.ENDPOINTKEY = E.ENDPOINTKEY AND
E.SECURITYSYSTEMKEY = S.SYSTEMKEY AND
E.ENDPOINTNAME = 'TETRAAD' AND TRIM(COALESCE(A.CUSTOMPROPERTY2,' ')) <> ' ' AND TRIM(COALESCE(U.EMAIL,' ')) = ' '
08/02/2024 04:18 PM
SELECT
U.SYSTEMUSERNAME AS 'SAV_USERNAME',
U.FIRSTNAME,
U.LASTNAME,
A.CUSTOMPROPERTY2 AS 'AD_UPN',
U.EMAIL as 'SAV_UPN',
E.ENDPOINTNAME AS 'Endpoint',
U.DEPARTMENTNAME as 'DEPARTMENT',
E.ENDPOINTKEY
FROM
USERS U
JOIN
USER_ACCOUNTS UA ON U.USERKEY = UA.USERKEY
JOIN
ACCOUNTS A ON A.ACCOUNTKEY = UA.ACCOUNTKEY
JOIN
ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
JOIN
SECURITYSYSTEMS S ON E.SECURITYSYSTEMKEY = S.SYSTEMKEY
WHERE
E.ENDPOINTNAME = 'TETRAAD' AND
TRIM(COALESCE(A.CUSTOMPROPERTY2, '')) <> '' AND
TRIM(COALESCE(U.EMAIL, '')) = ''