Report to find the duplicate accounts and information related to those accounts

[prasannta]

Use Case

Need a report to find the duplicate accounts and information related to those accounts, like the users associated with those accounts and their corresponding statuses and email addresses for a particular endpoint.

Pre-requisites

N/A

Applicable Version(s)

All

Solution

• The following query will give you information related to accounts for a particular endpoint:

 SELECT
username AS 'USER_NAME',
users.FIRSTNAME AS 'FIRST_NAME',
users.LASTNAME AS 'LAST_NAME',
users.EMAIL as 'USERS_EMAIL',
CASE STATUSKEY
WHEN '0' THEN 'Inactive'
WHEN '1' THEN 'Active'
END AS 'USER_STATUS',
accounts.name AS 'ACCOUNT_NAME',
accounts.DISPLAYNAME AS 'ACCOUNT_DISPLAY_NAME',
CASE accounts.status
WHEN '1' THEN 'ACTIVE'
WHEN '2' THEN 'INACTIVE'
WHEN 'Active' THEN 'Active'
WHEN 'Inactive' THEN 'Inactive'
WHEN 'In-active' THEN 'In-active'
WHEN 'Manually Provisioned' THEN 'Manually Provisioned'
WHEN 'Manually Suspended' THEN 'Manually Suspended'
WHEN 'SUSPENDED FROM IMPORT SERVICE' THEN 'SUSPENDED FROM IMPORT SERVICE'
END AS 'ACCOUNT_STATUS',
endpoints.ENDPOINTKEY AS 'ENDPOINT_KEY',
endpoints.ENDPOINTNAME AS 'ENDPOINT'
FROM
users,
user_accounts,
accounts,
endpoints
WHERE
users.userkey = user_accounts.USERKEY
AND user_accounts.ACCOUNTKEY = accounts.ACCOUNTKEY
AND accounts.ENDPOINTKEY = endpoints.ENDPOINTKEY
AND endpoints.ENDPOINTKEY = <<EndPointKey>>;

• You can use the below query to get the duplicate accounts per endpoint:

 SELECT
COUNT(t.accountkey) AS 'TOTAL_NO_OF_ACCOUNTS',
ACCOUNT_NAME,
CASE status
WHEN '1' THEN 'ACTIVE'
WHEN '2' THEN 'INACTIVE'
WHEN 'Active' THEN 'Active'
WHEN 'Inactive' THEN 'Inactive'
WHEN 'In-active' THEN 'In-active'
WHEN 'Manually Provisioned' THEN 'Manually Provisioned'
WHEN 'Manually Suspended' THEN 'Manually Suspended'
WHEN 'SUSPENDED FROM IMPORT SERVICE' THEN 'SUSPENDED FROM IMPORT SERVICE'
END AS 'ACCOUNT_STATUS',
ENDPOINT_KEY,
ENDPOINT
FROM
(SELECT
username AS 'USER_NAME',
users.FIRSTNAME AS 'FIRST_NAME',
users.LASTNAME AS 'LAST_NAME',
users.EMAIL AS 'USERS_EMAIL',
CASE STATUSKEY
WHEN '0' THEN 'Inactive'
WHEN '1' THEN 'Active'
END AS 'USER_STATUS',
accounts.accountkey,
accounts.name AS 'ACCOUNT_NAME',
accounts.DISPLAYNAME AS 'ACCOUNT_DISPLAY_NAME',
accounts.status,
endpoints.ENDPOINTKEY AS 'ENDPOINT_KEY',
endpoints.ENDPOINTNAME AS 'ENDPOINT'
FROM
users, user_accounts, accounts, endpoints
WHERE
users.userkey = user_accounts.USERKEY
AND user_accounts.ACCOUNTKEY = accounts.ACCOUNTKEY
AND accounts.ENDPOINTKEY = endpoints.ENDPOINTKEY
AND endpoints.ENDPOINTKEY = <<EndPointKey>>) t
GROUP BY ACCOUNT_NAME , status , ENDPOINT_KEY

HAVING count(t.accountkey)>1;

• Once this is done and in case you see any duplicate accounts, you can run the below query to get the account details:

 SELECT
username AS 'USER_NAME',
u2.FIRSTNAME AS 'FIRST_NAME',
u2.LASTNAME AS 'LAST_NAME',
u2.email AS 'EMAIL_ADDRESS',
CASE STATUSKEY
WHEN '0' THEN 'Inactive'
WHEN '1' THEN 'Active'
END AS 'USER_STATUS',
accounts.name AS 'ACCOUNT_NAME',
accounts.DISPLAYNAME AS 'ACCOUNT_DISPLAY_NAME',
ENDPOINT_KEY,
ENDPOINT
FROM
(SELECT
COUNT(t.accountkey) AS 'TOTAL_NO_OF_ACCOUNTS',
ACCOUNT_NAME,
CASE status
WHEN '1' THEN 'ACTIVE'
WHEN '2' THEN 'INACTIVE'
WHEN 'Active' THEN 'Active'
WHEN 'Inactive' THEN 'Inactive'
WHEN 'In-active' THEN 'In-active'
WHEN 'Manually Provisioned' THEN 'Manually Provisioned'
WHEN 'Manually Suspended' THEN 'Manually Suspended'
WHEN 'SUSPENDED FROM IMPORT SERVICE' THEN 'SUSPENDED FROM IMPORT SERVICE'
END AS 'ACCOUNT_STATUS',
ENDPOINT_KEY,
ENDPOINT
FROM
(SELECT
username AS 'USER_NAME',
users.FIRSTNAME AS 'FIRST_NAME',
users.LASTNAME AS 'LAST_NAME',
users.EMAIL AS 'USERS_EMAIL',
CASE STATUSKEY
WHEN '0' THEN 'Inactive'
WHEN '1' THEN 'Active'
END AS 'USER_STATUS',
accounts.accountkey,
accounts.name AS 'ACCOUNT_NAME',
accounts.DISPLAYNAME AS 'ACCOUNT_DISPLAY_NAME',
accounts.status,
endpoints.ENDPOINTKEY AS 'ENDPOINT_KEY',
endpoints.ENDPOINTNAME AS 'ENDPOINT'
FROM
users, user_accounts, accounts, endpoints
WHERE
users.userkey = user_accounts.USERKEY
AND user_accounts.ACCOUNTKEY = accounts.ACCOUNTKEY
AND accounts.ENDPOINTKEY = endpoints.ENDPOINTKEY
AND endpoints.ENDPOINTKEY = <<EndPointKey>>) t
GROUP BY ACCOUNT_NAME , status , ENDPOINT_KEY
HAVING COUNT(t.accountkey) > 1) t2
LEFT JOIN
accounts ON (t2.ACCOUNT_NAME = accounts.name
AND accounts.ENDPOINTKEY = t2.ENDPOINT_KEY)
LEFT JOIN
user_accounts ON accounts.ACCOUNTKEY = user_accounts.ACCOUNTKEY
LEFT JOIN
users u2 ON user_accounts.USERKEY = u2.USERKEY;

References