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

Analytics query to find users where manager attribute is not matching with AD manager attribute

Diwakar
New Contributor III
New Contributor III

Hi, We have requirement to identify those set of users where manager attribute is not matching within AD end-point.

In Saviynt Manager attribute is being updated in Owner's attribute and Manager however in AD end-point its updated as Custom Property 19 and manager value is being passed as 'CN'

So please suggest how to compare manager's attribute between Saviynt user mapping and AD end-point mapping.

21 REPLIES 21

pmahalle
All-Star
All-Star

Hi @Diwakar ,

Do you mean in your acxount's CP19 it's manager's DN and you want to compare whether DN is of the same user present in owner field of  user?


Pandharinath Mahalle(Paddy)
If this reply answered your question, please Accept As Solution to help other who may have a same problem. Give Kudos 🙂

Diwakar
New Contributor III
New Contributor III

@pmahalle Yes correct, so we want to find those users list where manager's is different when compared to AD within Saviynt. Let me know if we can get such list through Data analyzer.

Thanks,

Diwakar.

dgandhi
All-Star
All-Star

I would suggest below, store the CN attribute of the Manager on user profile (say cp 21)

Post that you can write SQL query and find list of users for whom CP21 of user doesn't match with CP19 of accounts.

 

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.

Diwakar
New Contributor III
New Contributor III

@dgandhi Can You provide the sample?

Thanks,

Diwakar.

Please try below?

dgandhi_0-1706535578492.png

 

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.

Diwakar
New Contributor III
New Contributor III

@dgandhi I tried below query however I got syntax error. Please assist.

select u.username from saviynt.accounts a
left join saviynt.user_accounts ua
on a.accountkey=ua.accountkey
left join saviynt.users u
on ua.userkey=u.userkey
where a.customproperty19 !=u.customproperty19
and a.endpointkey= (select e.endpointkey from saviynt.endpoints e where e.endpointname='Active Directory'

Diwakar_0-1706539451682.png

 

select u.username from saviynt.accounts a
left join saviynt.user_accounts ua
on a.accountkey=ua.accountkey
left join saviynt.users u
on ua.userkey=u.userkey
where a.customproperty19 !=u.customproperty19
and a.endpointkey= (select e.endpointkey from saviynt.endpoints e where e.endpointname='Active Directory')

Added missing ) at the end.

Run in analytics and see what is the result

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.

Diwakar
New Contributor III
New Contributor III

@dgandhi Thanks this time query does work however it's not giving the right output which I am looking. From below output screenshot you can see from AD side we are only storing manager value(customproperty19) is through CN so with this query it seems manager attribute are incorrectly compared with saviynt user's manager. I am looking someway to compare the manager data between Saviynt User and AD which is provisioned by Saviynt. Hence please suggest accordingly.

Diwakar_0-1706551825325.png

 

This was the complete thing which I had proposed.

I would suggest below, store the CN attribute of the Manager on user profile (say cp 21) --> This you need to do.

Post that you can write SQL query and find list of users for whom CP21 of user doesn't match with CP19 of accounts. --> For this above query is shared.

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.

rushikeshvartak
All-Star
All-Star

You can do with below query add required endpoint filters 

SELECT u.username,
       u.owner,
       a.NAME,
       a.customproperty19                        AS manager_cp19,
       (SELECT u1.username
        FROM   accounts a1,
               user_accounts ua1,
               users u1,
               endpoints e1
        WHERE  a1.accountkey = ua1.accountkey
               AND u1.userkey = ua1.userkey
               AND e1.endpointkey = a1.endpointkey
               AND a1.NAME = a.customproperty19) AS managerusername
FROM   accounts a,
       user_accounts ua,
       users u,
       endpoints e
WHERE  a.accountkey = ua.accountkey
       AND u.userkey = ua.userkey
       AND e.endpointkey = a.endpointkey 


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

Diwakar
New Contributor III
New Contributor III

@rushikeshvartak Thanks for the query, however manager username i am getting as null. Can you please check and help me with the updated query. If I get manager username from AD accounts table then we can compare the same with Saviynt user table.

Diwakar_1-1706855382374.png

Thanks,

Diwakar.

 

share screenshot of account printed in manager_cp19


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

Diwakar
New Contributor III
New Contributor III

@rushikeshvartak Here is the screenshot below as requested. Request your help to get the right query for this.

Diwakar_0-1706856452776.png

 

share result of below query 

select * from accounts where accountkey=<<accountkey from above screenshot>>


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

Diwakar
New Contributor III
New Contributor III

@rushikeshvartak I put account key of user from screenshot shared, and then ran below query. Below is the output for the same.

select * from accounts where accountkey=23383

Diwakar_0-1707134025871.png

 

Please suggest next so that we can compare the manager between Saviynt User's manager and AD.

CR
Regular Contributor III
Regular Contributor III

@Diwakar,

try below query and modify based on display columns

select u.username,a.ACCOUNTID,u.customproperty19 from accounts a
left join user_accounts ua
on a.accountkey=ua.accountkey
left join users u
on ua.userkey=u.userkey
where a.ACCOUNTID !=u.customproperty19
and a.endpointkey= (select e.endpointkey from endpoints e where e.endpointname=' ')


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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Diwakar
New Contributor III
New Contributor III

@CR Hi Raghu, it is not giving me correct output as because u.customproperty19 is not the manager attribute, customproperty19 is manager CN for AD account. Please help me get the query accordingly where I can compare user's Manager from Saviynt user table and Manager CN(customproperty19) from accounts table.

 

SELECT u.username,
       u.owner,
       a.NAME,
       a.customproperty19                        AS manager_cp19,
       (SELECT u1.username
        FROM   accounts a1,
               user_accounts ua1,
               users u1,
               endpoints e1
        WHERE  a1.accountkey = ua1.accountkey
               AND u1.userkey = ua1.userkey
               AND e1.endpointkey = a1.endpointkey
               AND a1.accountid= a.customproperty19) AS managerusername
FROM   accounts a,
       user_accounts ua,
       users u,
       endpoints e
WHERE  a.accountkey = ua.accountkey
       AND u.userkey = ua.userkey


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

Diwakar
New Contributor III
New Contributor III

@rushikeshvartak Now getting this error. Please suggest next. I think we are very close to the solution.

Diwakar_0-1707204684250.png

Error: Subquery returns more than 1 row

 

SELECT u.username,
       u.owner,
       a.name,
       a.customproperty19 AS manager_cp19,
       (SELECT u1.username
        FROM   accounts a1
               JOIN user_accounts ua1
                 ON a1.accountkey = ua1.accountkey
               JOIN users u1
                 ON u1.userkey = ua1.userkey
               JOIN endpoints e1
                 ON e1.endpointkey = a1.endpointkey
        WHERE  a1.accountid = a.customproperty19
               AND e1.endpointname = 'ABCD'
        LIMIT  1)         AS managerusername
FROM   accounts a
       JOIN user_accounts ua
         ON a.accountkey = ua.accountkey
       JOIN users u
         ON u.userkey = ua.userkey
       JOIN endpoints e
         ON e.endpointkey = a.endpointkey; 


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

Thanks a lot @rushikeshvartak the query finally worked. Really appreciate your efforts.