Summary

This article provides information about, how to fix the issue of AD organization or AD User listing due to duplicate records exists in the database.

Applies To

MachPanel v7 and above

Issue Details

We have optimized AD Org and User listings by pre-processing records and keeping them in new table. It should work for clients but at times clients have some garbage/duplicate records that can break things and error can be seen on AD Organization or AD User listing like below:

Resolution

Below queries can be used to find duplicates in ADUser and Mailbox table, have to remove and keep only 1 working record in specific table:

--Duplicate in ADirUsers, if found have to keep one record for 1 LDAP url.

Select * from HB_tblADirUsers
Where UserLDAPURL in
(
Select UserLDAPURL from HB_tblADirUsers
Where ADUserID not in (Select ADUserId from HB_tblADirUserGroupJobs Where StatusId not in (2,3))
and UserLDAPURL!=''
Group By UserLdapURL
Having Count(UserLDAPURL) > 1
)
order by UserLDAPURL asc
GO

--Duplicate in tblExchangeMailboxes against UserLDAP, if found have to keep one record for 1 LDAP url.

Select * from HB_tblExchangeMailboxes
Where UserLDAPUrl in
(
Select UserLDAPURL from HB_tblExchangeMailboxes
Where MailboxID not in (Select AccountId from HB_tblExchangeGroupJobs Where AccountType=0 and StatusId not in (2,3))
and UserLDAPURL!=''
Group By UserLdapURL
Having Count(UserLDAPURL) > 1
)
order by UserLDAPURL asc
GO

--Duplicate in tblExchangeMailboxes against ADUserId, if found have to keep one record for 1 ADUserId

Select * from HB_tblExchangeMailboxes
Where ADUserID in
(
Select ADUserID from HB_tblExchangeMailboxes
Where MailboxID not in (Select AccountId from HB_tblExchangeGroupJobs Where AccountType=0 and StatusId not in (2,3))
Group By ADUserID
Having Count(ADUserID) > 1
)
order by MailboxID asc
GO
Once all duplicates are removed, run below script:

Declare @RowsCount int
Set @RowsCount = 1
while @RowsCount != 0
Begin
                Exec CSP_UpdateHostedOrgExtendedData
                Set @RowsCount = @@ROWCOUNT
End
GO

Declare @RowsCount int
Set @RowsCount = 1
while @RowsCount != 0
Begin
                Exec CSP_UpdateHostedUserExtendedData
                Set @RowsCount = @@ROWCOUNT
End
GO

Important: Rules to follow when deleting from aduser:

• If AdUser has 2 different CustomerId, keep the record which has highest value for ADUserId.
• If CustomerId is same, check for ADUserid in Mailbox table, keep record which is in mailbox table.
  • If no enterprise service enable on ADUser, keep latest record.