This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

I am trying to identify duplicate email addresses in the Active Directory Mail attribute. Attempted using GROUP BY and HAVING COUNT(id) >1 but it is not working, any ideas on what I could be doing wrong?

SELECT [base].[DomainName] as [Domain Name (Domain)], [tblDomainOU].[CanonicalName] as [Canonical Name (Domain OU)], CASE WHEN LEN([tblAccount].[SAMAccountDomain]) = 0 THEN [tblAccount].[SAMAccountName] ELSE [tblAccount].[SAMAccountDomain] + '\' + [tblAccount].[SAMAccountName] END as [Account Name (Account)], [tblDomainUser].[HomeDir] as [Home Directory (Domain User)], [tblDomainUser].[AccountIsDisabled] as [Is Disabled (Domain User)], [tblDomainUser].[AccountIsLocked] as [Is Locked (Domain User)], [tblDomainUser].[EmailAddress] as [Email Address (Domain User)]

FROM

[tblDomain] base

LEFT OUTER JOIN ([tblDomainUser] tblDomainUser FULL OUTER JOIN [tblDomainUserPart3] tblDomainUser_S0

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S0].[DomainUserID])

FULL OUTER JOIN [tblDomainUserPart2] tblDomainUser_S1

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S1].[DomainUserID])

FULL OUTER JOIN [tblDomainUser67bea9ee-383e-4ee6-9be6-15dcc2cc0d12] tblDomainUser_S2

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S2].[DomainUserID])

FULL OUTER JOIN [tblDomainUserb754719b-c532-4c65-b537-7d28ff3e8b0e] tblDomainUser_S3

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S3].[DomainUserID])

FULL OUTER JOIN [tblDomainUserExchange] tblDomainUser_S4

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S4].[DomainUserID])

FULL OUTER JOIN [tblDomainUseredf83d03-0e20-4d05-892f-b3e588a9b0ad] tblDomainUser_S5

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S5].[DomainUserID])

FULL OUTER JOIN [tblDomainUserabf2d8ad-3a78-4bb5-82ae-d739246cffdd] tblDomainUser_S6

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S6].[DomainUserID])

FULL OUTER JOIN [tblDomainUser17e0e9a0-32d0-431a-a407-6822157f1e78] tblDomainUser_S7

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S7].[DomainUserID])

FULL OUTER JOIN [tblDomainUserdbf5c43a-97a9-42e6-9580-2de371bbb01e] tblDomainUser_S8

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S8].[DomainUserID])

FULL OUTER JOIN [tblDomainUser1c211fe2-c301-45c0-8e99-9d7e37a44049] tblDomainUser_S9

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S9].[DomainUserID])

FULL OUTER JOIN [tblDomainUser69057736-bd06-4375-bb0e-594eb7b30036] tblDomainUser_S10

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S10].[DomainUserID])

FULL OUTER JOIN [tblDomainUsere3da4b16-ec71-411b-bb3b-4f43599d0ff4] tblDomainUser_S11

ON ([tblDomainUser].[DomainUserID] = [tblDomainUser_S11].[DomainUserID]))

ON ([base].[DomainID] = [tblDomainUser].[DomainID] AND ([tblDomainUser].[LDM_TOMBSTONED] = 0 OR [tblDomainUser].[LDM_TOMBSTONED] IS NULL))

LEFT OUTER JOIN [tblAccount] tblAccount

ON ([tblDomainUser].[AccountID] = [tblAccount].[AccountID] AND ([tblAccount].[LDM_TOMBSTONED] = 0 OR [tblAccount].[LDM_TOMBSTONED] IS NULL))

LEFT OUTER JOIN [tblDomainOU] tblDomainOU

ON ([tblDomainUser].[DomainOUID] = [tblDomainOU].[DomainOUID] AND ([tblDomainOU].[LDM_TOMBSTONED] = 0 OR [tblDomainOU].[LDM_TOMBSTONED] IS NULL))

WHERE ((base.LDM_TOMBSTONED = 0 OR base.LDM_TOMBSTONED IS NULL))

ORDER BY [base].[DomainName] ASC, [tblDomainOU].[CanonicalName] ASC, [Account Name (Account)] ASC

GROUP BY [Email Address (Domain User)] HAVING COUNT(id) [Email Address (Domain User)] >1

Parents Reply Children
No Data