Need a query that reports all the duplicate accounts that the user is having

[sharu]

Hi Team,

Need a query that displays all the duplicate accounts that the user is having along with account status and endpoint name .

Thanks ,

Sharanya V

[rushikeshvartak]

SELECT
a.name,
ep.endpointname,
ep.endpointKey,
a.lastlogondate AS 'Last Logon',
MONTHNAME(a.lastlogondate) AS lastlogonmonth,
CASE WHEN a.status = 1 THEN 'Active' ELSE 'Inactive' END AS 'Account Status'
FROM
accounts a
INNER JOIN
endpoints ep ON a.ENDPOINTKEY = ep.ENDPOINTKEY
INNER JOIN
USER_ACCOUNTS UA ON UA.accountkey = a.accountkey
INNER JOIN
USERS U ON UA.userkey = U.userkey
GROUP BY
a.name,
ep.endpointname,
a.status
HAVING
COUNT(U.username) > 1;

[sharu]

Hi Rushikesh,

I need the user details and all the entries of duplicate accounts that the user is having for all the endpoints. The above query is not displaying all the entries of duplicate accounts that the user is having.

Thanks,

SharanyaV

[rushikeshvartak]

Elaborate with example

SELECT
u.username,
u.userkey,
a.name AS account_name,
ep.endpointname,
ep.endpointKey,
a.lastlogondate AS 'Last Logon',
MONTHNAME(a.lastlogondate) AS lastlogonmonth,
CASE WHEN a.status = 1 THEN 'Active' ELSE 'Inactive' END AS 'Account Status'
FROM
users u
INNER JOIN
user_accounts ua ON u.userkey = ua.userkey
INNER JOIN
accounts a ON ua.accountkey = a.accountkey
INNER JOIN
endpoints ep ON a.endpointkey = ep.endpointkey
WHERE
u.username IN (
SELECT u.username
FROM users u
INNER JOIN user_accounts ua ON u.userkey = ua.userkey
INNER JOIN accounts a ON ua.accountkey = a.accountkey
GROUP BY u.username, u.userkey
HAVING COUNT(DISTINCT a.endpointkey) > 1
)
ORDER BY
u.username, ep.endpointname, a.name;

[sharu]

Hi @rushikeshvartak ,

I need only the duplicate entries only. For eg: if a user is   having two accounts provisioned to same system , it should display both the entries of the accounts .

But in the above query it is displaying all the duplicate and non duplicate accounts that the user is having.

Thanks,

Sharanya V

[rushikeshvartak]

SELECT
    u.username,
    u.userkey,
    a.name AS account_name,
    ep.endpointname,
    ep.endpointKey,
    a.lastlogondate AS 'Last Logon',
    MONTHNAME(a.lastlogondate) AS lastlogonmonth,
    CASE WHEN a.status = 1 THEN 'Active' ELSE 'Inactive' END AS 'Account Status'
FROM
    users u
INNER JOIN
    user_accounts ua ON u.userkey = ua.userkey
INNER JOIN
    accounts a ON ua.accountkey = a.accountkey
INNER JOIN
    endpoints ep ON a.endpointkey = ep.endpointkey
WHERE
    u.username IN (
        SELECT u.username
        FROM users u
        INNER JOIN user_accounts ua ON u.userkey = ua.userkey
        INNER JOIN accounts a ON ua.accountkey = a.accountkey
        GROUP BY u.username, ep.endpointkey
        HAVING COUNT(a.accountkey) > 1
    )
ORDER BY
    u.username, ep.endpointname, a.name;

[sharu]

Hi @rushikeshvartak ,

SELECT
    u.username,
    u.userkey,
    a.name AS account_name,
    ep.endpointname,
    ep.endpointKey,
    a.lastlogondate AS 'Last Logon',
    MONTHNAME(a.lastlogondate) AS lastlogonmonth,
    CASE WHEN a.status = 1 THEN 'Active' ELSE 'Inactive' END AS 'Account Status'
FROM
    users u
INNER JOIN
    user_accounts ua ON u.userkey = ua.userkey
INNER JOIN
    accounts a ON ua.accountkey = a.accountkey
INNER JOIN
    endpoints ep ON a.endpointkey = ep.endpointkey
WHERE
    u.username IN (
        SELECT u.username
        FROM users u
        INNER JOIN user_accounts ua ON u.userkey = ua.userkey
        INNER JOIN accounts a ON ua.accountkey = a.accountkey
        GROUP BY u.username, ep.endpointkey
        HAVING COUNT(a.accountkey) > 1
    )
ORDER BY
    u.username, ep.endpointname, a.name;

While validating the above query results, it provides all the accounts that the user is having. It should only display the duplicate accounts only. But in this case both duplicate and non duplicate accounts are displaying.

Thanks,

Sharanya V

[rushikeshvartak]

Share screenshot and highlight what should be there and what not. and in result on which basis some account should not be shown ?

[sharu]

Hi @rushikeshvartak ,

Please check the below screenshot:

Consider the above date , so the query should display only the highlighted ones where there is two accounts found for the same system/endpoint , but in this case it is displaying all the accounts including the non-highlighted ones.

Thanks,

Sharanya V

[rushikeshvartak]

Example is wrong as account name is unique and can’t be same / are you considering SFIS status account?

[sharu]

@rushikeshvartak This is just eg where I'm showing account name unique, but account names are different for each system, but I'm looking something where the query identifies user having more than one  accounts in the same system/endpoint, it could either be active or inactive one , but the query must display more than one account only and ignore the other accounts which are not more than one.

Regards,

Sharanya V

[Deven]

Hello @sharu 

I hope the below query will help you 

Select u.USERNAME, e.endpointname, group_concat(a.name order by a.name asc) accountname, group_concat(a.status order by a.name asc) accountstatus from accounts a join endpoints e ON a.endpointkey=e.endpointkey left join user_accounts ua ON ua.accountkey=a.accountkey left join users u ON u.userkey=ua.userkey where e.endpointname in ('XYZ') and a.status not in ('SUSPENDED FROM IMPORT SERVICE', '2') and u.userkey is not null group by u.username, e.endpointname having count(u.username)>1

Thanks & Regards 

Deven Karande

[sharu]

Hi Devan ,

Thanks, for the query, I need the results to be displayed in a single line instead of concating them in one single line and also the report is displaying both active and inactive accounts on a particular system. If one account is active and the other is inactive account, it should display only the active account and not the inactive accounts.

Can you please share the remodified query.

Thanks,

Sharanya V

[rushikeshvartak]

SELECT u.username,
       e.endpointname,
       Group_concat(a.name ORDER BY a.name ASC)   AS accountname,
       Group_concat(a.status ORDER BY a.name ASC) AS accountstatus
FROM   accounts a
       JOIN endpoints e
         ON a.endpointkey = e.endpointkey
       LEFT JOIN user_accounts ua
              ON ua.accountkey = a.accountkey
       LEFT JOIN users u
              ON u.userkey = ua.userkey
WHERE  e.endpointname IN ( 'XYZ' )
       AND a.status NOT IN ( 'SUSPENDED FROM IMPORT SERVICE', '2' )
       AND u.userkey IS NOT NULL
GROUP  BY u.username,
          e.endpointname
HAVING Count(DISTINCT CASE
                        WHEN a.status IN (1, 'ACTIVE') THEN 1
                      end) > 0
       AND Count(DISTINCT CASE
                            WHEN a.status  IN (2, 'INACTIVE') THEN 1
                          end) = 0; 

[PremMahadikar]

Hi @sharu ,

This should be your query.

Select 
	u.USERNAME, 
	CASE 
		WHEN u.statuskey = 1 THEN 'Active' ELSE 'Inactive' 
	END AS 'User Status',
	a.name as Accountname,
	CASE 
		WHEN a.status = 1 THEN 'Active'
		WHEN a.status = 2 THEN 'Iactive'
		ELSE a.status 
	END AS 'Account Status'
from 
	accounts a left join user_accounts ua ON ua.accountkey=a.accountkey 
	left join users u ON u.userkey=ua.userkey 
	a.ENDPOINTKEY=<10>ENDPOINTKEY
group by a.name having count(a.name)>1
order by u.username asc

If this is helpful, please consider selecting Accept As Solution and hit Kudus