Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Query to get users with multiple accounts from one endpoint

janB
New Contributor III
New Contributor III

Hello Team, 

Having trouble to identify Users which have more then one account within one endpoint. 

Is it possible to find them ? 

Reason - we have ADSI connector importing accounts. Users are coming from HR system. 90% processes work fine , but in same cases we have problems because there are users having more then 1 AD account linked to them. They are in fact linked correctly - re-hires , wrongly created accounts  .... 

To do some corrections we need to get these users .... 

Thank you all for your help. 

Jan

8 REPLIES 8

NM
Esteemed Contributor
Esteemed Contributor

Hi @janB ,

Select u.username, a.name from users u, user_accounts ua , accounts a where u.userkey=ua.userkey and ua.accountkey= a.accountkey and a.endpointkey=your endpoint key and group by user_accounts.userkey having count(*)>1


If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'

rushikeshvartak
All-Star
All-Star

SELECT u.username,
       u.firstname,
       u.lastname,
       a.endpointkey,
       Count(u.username) AS account_count
FROM   accounts a
       JOIN user_accounts ua
         ON ua.accountkey = a.accountkey
       JOIN users u
         ON ua.userkey = u.userkey
WHERE  a.endpointkey = 'AD_ENDPOINT_KEY'
GROUP  BY u.username,
          u.firstname,
          u.lastname,
          a.endpointkey
HAVING Count(u.username) > 1 


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

janB
New Contributor III
New Contributor III

Thank YOU! 

The query worked well. 

Jan !

Amit_Malik
Valued Contributor II
Valued Contributor II

Hi @janB ,

This should work.

Select u.username, acc.name from
users u
JOIN user_accounts ua ON u.userkey=ua.userkey
JOIN accounts acc ON ua.accountkey=acc.accountkey
JOIN endpoints e ON e.endpointkey=acc.endpointkey
where e.endpointname='ABC' group by u.username, acc.name having count(acc.name) > 1

 

 

Kind Regards,
Amit Malik
If this helped you move forward, please click on the "Kudos" button.
If this answers your query, please select "Accept As Solution".

janB
New Contributor III
New Contributor III

Hello Amit, 

Thank you for your post. 

Unfortunately I received 0 results. 

BR! 

Amit_Malik
Valued Contributor II
Valued Contributor II

Np, good thing is that something worked for you that is what matters. Below is same query working for me. In this you need to pass endpoint name and not key. Names are more user friendly.

Amit_Malik_0-1725350488974.png

 

Kind Regards,
Amit Malik
If this helped you move forward, please click on the "Kudos" button.
If this answers your query, please select "Accept As Solution".

@Amit_Malik  You have group by account name and account name can't be same in same endpoint hence your query will not work


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Okay, the query finds out users with duplicate accounts in endpoint for users.

The screen shot that I have shared are the users with same account name in endpoint but more than one. Account is is different

 

It seems the requirement was to find out users with more than one account. 

 

Kind Regards,
Amit Malik
If this helped you move forward, please click on the "Kudos" button.
If this answers your query, please select "Accept As Solution".