Users having AD UPN and Empty value in Saviynt

[Pratham]

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

[rahul_p · Accepted solution]

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.

[Pratham]

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,' ')) = ' '

[rushikeshvartak]

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, '')) = ''