Monitoring Active Directory Import for SharePoint 2013 User Profiles

Managing user profiles in SharePoint 2010 presented many challenges to administrators.  If you ever had to troubleshoot user profile imports in SharePoint 2010, you probably quickly realized that the FIM synchronization client was an invaluable resource.  SharePoint 2013 has made some significant strides toward addressing user profile synchronization pain points.  One of the major changes in SharePoint 2013, with respect to user profile imports/synchronizations, is the reintroduction of the Active Directory Import.  This provides a straight-forward, one-way synchronization of user profile information from Active Directory into SharePoint.  It completely removes the need for the FIM Windows service (hold the applause, please) and, in many cases, will meet the customer's needs for SharePoint profiles.  Due to limited space, I will assume that you have configured the SharePoint Active Directory Import and go straight to the point of this blog post, which is monitoring/troubleshooting your synchronizations.

If you used a default farm configuration, the Diagnostic Logging settings for the user profile components (SharePoint Server --> User Profile and SharePoint Server --> User Profile Manager) will be set to "Information" and "Medium" for Event Level and Trace Level, respectively.  This controls the detail that is logged to the event logs and the ULS logs and is no different than logging/troubleshooting for any other component in SharePoint.  This is good when you are troubleshooting an on-going/recent issue.  Instead of the "incremental synchronization" timer job, you will find a timer job titled "<UPA name> - User Profile ActiveDirectory Import Job".  There is really not a big change from SharePoint 2010 to 2013 in these areas.  However, these resources do not provide you with the same level of details that you could obtain via the FIM synchronization client.  So how do you find those details, such as how many additions/changes/deletes occurred?  Or how many profiles were imported?  Read on to find out.

First, you will need to ensure that the Usage and Health Data Collection Service Application is configured.  This will create a database that collects/stores a variety of usage information for pre-defined periods (periods are configurable).  If you browse to the "Configure Usage and Health Data Collection" page in Central Administration, you will notice that you have the option to select an event type called "User Profile ActiveDirectory Import Usage".  Ensure that the "Enable usage data collection" checkbox is selected.  Select the "User Profile ActiveDirectory Import Usage" event type, if it is not already selected and click "Ok".  This will cause FIM-like details from the user profile synchronization to be logged to the Usage database.  This database is the one database in SharePoint that supports custom read/write operations.  Within the Usage database, there is a set of partitioned tables named "UserProfileADImport_Partition#".  Additionally, you will find a view named "UserProfileADImport".  You can use the PowerShell script below to query the "UserProfileADImport" view (script is provided As Is - use at your own risk).  This view will aggregate the individual partition tables and provide you with detailed information about each user profile synchronization (one row per import).  So while this is arguably a little more complicated than simply opening the FIM synchronization client, it does provide the same level of details without the hassle of dealing with the FIM Windows service for user profile synchronizations.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

function Run-SQLQuery ($SqlServer, $SqlDatabase, $SqlQuery) {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server =" + $SqlServer + "; Database =" + $SqlDatabase + "; Integrated Security = True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $SqlQuery
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $SqlConnection.Close()
    $DataSet.Tables[0]
}

$server = "<db server>"
$usagedb = "<usage db>"

Run-SQLQuery -SqlServer $server -SqlDatabase $usagedb -SqlQuery "SELECT * from [dbo].[UserProfileADImport]" | Format-List