Use Case
How to check if an account is wrongly linked to multiple users?
Pre-requisites
Analytics, Accounts
All
In order to check if an account is linked to multiple user accounts we need to configure the below query in v2 analytics:
SELECT NAME,
user_name,
count_userkeys,
endpoint_name
FROM (SELECT a.NAME,
ua.accountkey AS 'Account_Key',
Group_concat(u.username) AS 'USER_NAME',
Count(ua.userkey) AS 'COUNT_USERKEYS',
et.endpointname AS 'Endpoint_Name'
FROM user_accounts ua,
users u,
accounts a,
endpoints et
WHERE u.userkey = ua.userkey
AND a.accountkey = ua.accountkey
AND a.endpointkey = et.endpointkey
GROUP BY ua.accountkey
HAVING Count(u.username) > 1) t
Please note that this query will combine the user keys that are associated with a particular account, so in order to check which users are wrongly linked to the accounts, you need to take the input(COUNT_USERKEYS) from the above query and then run another query as the one given below:
SELECT username,
statuskey,
city,
location
FROM users
WHERE username IN (SELECT count_userkeys
FROM (SELECT NAME,
user_name,
count_userkeys,
endpoint_name
FROM (SELECT a.NAME,
ua.accountkey AS
'Account_Key',
Group_concat(u.username) AS
'USER_NAME',
Count(ua.userkey) AS
'COUNT_USERKEYS'
,
et.endpointname AS 'Endpoint_Name'
FROM user_accounts ua,
users u,
accounts a,
endpoints et
WHERE u.userkey = ua.userkey
AND a.accountkey = ua.accountkey
AND a.endpointkey = et.endpointkey
GROUP BY ua.accountkey
HAVING Count(u.username) > 1) t) t2);
References
https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter17-EIC-Analytics/Managing-An...
