Requirement

In order to ensure that our Mysites and contact directory in SharePoint is 100% accurate, we periodically export all users from AD and SharePoint to lists and compare them using Microsoft Access to look for any missing or invalid accounts in SharePoint. This is especially important given our complex AD structure and thousands of users.

Unfortunately, most of the time we do pick up inconsistencies which need to be resolved manually.

This may not be the best way to achieve this, and I am by no means a Powershell Guru. But it works for me.

Solution

Export all users from AD.

Assuming that you import only ‘enabled users’ and accounts where ‘extensionattribute15 != true’. Otherwise, modify the ldap search filter to meet your requirements.

Open PowerShell and type the following command:

dsquery * -filter "(&(objectCategory=user)(!(extensionAttribute15=true))(&(sAMAccountType=805306368)(!(userAccountControl:1.2.840.113556.1.4.803:=2))))" -limit 0 | dsget user -c -display -dn > c:\Users.txt

This will output a list of all users, including their DN names to a text file on your computer called Users.txt.

Export all users from SharePoint

We use SharePoint Management Shell to export all users from the UPS database. Here is the code to do so, change the line in bold to match your environment:

Add-PSSnapin Microsoft.SharePoint.PowerShell
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server.UserProfiles")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
# Enter your SharePoint site URL here...
$site = new-object Microsoft.SharePoint.SPSite(http://sitecollection);
$ServiceContext = [Microsoft.SharePoint.SPServiceContext]::GetContext($site);
$ProfileManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext)
$AllProfiles = $ProfileManager.GetEnumerator()
$outFile = "SPUsers.txt"
foreach($profile in $AllProfiles)
{
$DisplayName = $profile.DisplayName
Write-Output $DisplayName | out-file $outfile -append
}
$site.Dispose()

This will export the display name of all users in the User Profile Service Application database into a text file called SPUsers.txt.

Copy the list data into Microsoft Excel.

You now need to copy data from both text files into Microsoft Excel.

  1. Open Microsoft Excel (I use 2010).
  2. Create two Worksheets, one called ‘AD Users’ the other called’ SP Users’.
  3. Paste the list of SP users from the text file to a column in the ‘SP Users’ worksheet.
  4. Paste a list of AD users  from the text file to a column in the ‘AD Users’ worksheet.
  5. You may need to format the text to columns in order to clean up the data (especially for the AD extract).  To do so:
  6. Select the entire column you just copied, on the ribbon go to Data -> Text to Columns.
  7. I use a fixed width to seperate the Distinguished Name (DN) column and the Display name.
  8. Once you have seperated both columns, you can use text filters under the DN column to remove OUs that are not being imported into SharePoint.

You should now have a clean list of AD and SharePoint users in a Microsoft Excel workbook. The workbook contains 2 worksheets, one called ‘AD Users’ and other called ‘SP Users’.

Import the data into Access for analysis.

Now, we use Microsoft Access and its query feature to find unmatched names between the two lists.

  1. Create a blank database using Microsoft Access (2010 for me).
  2. Import both worksheets from your Excel workbook into individual Tables called ‘AD Users’ and ‘SP Users’.
  3. Review the data in the tables and make sure they do not have any formatting errors.
  4. On the ribbon, go to Create -> Query Wizard and select the ‘Find Unmatched Query Wizard’.
  5. Select your two tables, matching the ‘Display name’ field in each table.
  6. Take note of which table you select first, as it will be compared AGAINST the other table. For e.g. If you select the SP Users table first, it will show you the results of users in SharePoint who are not in AD.

Now you can see which accounts are valid / invalid and make your necessary changes.

More information:

MSDN Search Filter Syntax

http://msdn.microsoft.com/en-us/library/windows/desktop/aa746475(v=vs.85).aspx