We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Need a report with users where the users manager email and cp35 of Hewittna accout mismatch

SriRanga
Regular Contributor
Regular Contributor

Hi Team,

Users manager email is stored in CP35 of Hewittna account. Now we wanted to find the users whose managers email does not match with CP35.

Could you please help me prepare expression to get the users manager so that I can match it with CP35 from users Hewittna account.

 

I have below query not sure if its correct but I need-

--------------------------------

select u.username, u.displayname as userDisplayname, m.displayname as ManagerDisplayname, m.email, a.customproperty35, e.displayname as EndPointName from users u, users m, accounts a, user_accounts ua, endpoints e where u.manager=m.userkey and m.userkey=ua.userkey and ua.accountkey=a.accountkey and a.endpointkey=e.endpointkey and u.manager!=a.customproperty35 and e.endpointkey='433'

--------------------------------

So with bold items in above query can you please help me to get below values-

- Users manager display name

-User manager email

Thanks,

Amit Aware

4 REPLIES 4

dgandhi
All-Star
All-Star

Hi @SriRanga 

Can you check below query:

select u.username, u.displayname as userDisplayname, m.displayname as ManagerDisplayname, m.email, a.customproperty35, e.displayname as EndPointName
from users u, users m, accounts a, user_accounts ua, endpoints e
where u.manager=m.userkey
and u.userkey=ua.userkey
and ua.accountkey=a.accountkey
and a.endpointkey=e.endpointkey
and m.email!=a.customproperty35
and a.endpointkey='433'

Thanks

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

Darshanjain
Saviynt Employee
Saviynt Employee

Hi

Can you try with the below query

SELECT
u.username,
u.displayname AS userDisplayname,
m.displayname AS ManagerDisplayname,
m.email,
a.customproperty35,
e.displayname AS EndPointName
FROM
users u
JOIN users m ON u.manager = m.userkey
JOIN user_accounts ua ON m.userkey = ua.userkey
JOIN accounts a ON ua.accountkey = a.accountkey
JOIN endpoints e ON a.endpointkey = e.endpointkey
WHERE
m.email != a.customproperty35
AND e.endpointkey = '433'

If user doesn’t have manager it will not list user


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

@rushikeshvartak -- do we have any other way for user who doesn’t have manager ?

 

Thanks