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
Comments
Anonymous
August 06, 2014
Interesting Article - This seems to explain why we cannot make sense of the new SharePoint 2013 usage reporting - yet. Thank you for sharing... :-)Anonymous
April 24, 2017
I realize the article has been written a few years ago but still hope Will might respond. I'm trying to troubleshoot a situation where several AD users are seemingly not getting imported into User profile service, although they should be. Those users are in the correct OUs (and not just one ou but several different ones) and not disabled, other users from the same OUs are imported fine. Yet when i run Set-SPProfileServiceApplication -GetNonImportedObjects -whatif these "live" users appear in the list as Not imported. When I run full profile sync and check usage database view UserProfileADImport the column ImportFailureCount shows almost 100 failures so I suppose those might be the users in question. Is there any way to get more detailed report of each import run, exactly which users were not imported and why? Thanks!- Anonymous
April 27, 2017
Step,Have you turned up logging on the User Profile category of events? If not, try turning that to Verbose and running your import. I just ran a quick test in a lab and generated ULS messages that are down to individual users in the sync. Here is an example...04/27/2017 21:36:47.90 OWSTIMER.EXE (0x0A14) 0x0C48 SharePoint Portal Server User Profiles aer4e Medium Exception while updating properties for 'WFLAB\wfeaser': Property: Url, Exception Microsoft.Office.Server.UserProfiles.PropertyNotDefinedException: Property Not Defined: Url. An administrator must create this property in the Profile Administration tool. at Microsoft.Office.Server.UserProfiles.UserProfile.get_Item(String strPropName) at Microsoft.Office.Server.UserProfiles.UserProfile.GetProfileValueCollection(String propName) at Microsoft.Office.Server.UserProfiles.UserProfile.BulkPropertiesUpdate(Int64 importExportId, Hashtable properties, String strAccountName). 441417ea-5195-4af6-b5bf-c595f8d06c32
- Anonymous
Anonymous
September 06, 2017
Cool article Will! Thanks for the information and script you shared.Christian