Query to get users with multiple accounts from one endpoint

[janB]

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

[NM]

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

[rushikeshvartak · Accepted solution]

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 

[janB]

Thank YOU! 

The query worked well. 

Jan !

[Amit_Malik]

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

[janB]

Hello Amit, 

Thank you for your post. 

Unfortunately I received 0 results. 

BR! 

[Amit_Malik]

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.

[rushikeshvartak]

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

[Amit_Malik]

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.