Extracting data from FIM Synchronization Service Run Profile log

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm

Analyzing run history logs is an essential tasks in ensuring proper operation of your FIM environment. Often we need to go beyond analysis by creating scripts to detect and even correct the issues encountered during a run. In this blog I will suggest and approach of how to extract data from the run profile history. As you will see the  approach is semi-automated in its current stage, but certainly can be improved and/or adapted to other scenarios. The approach will leverage SQL Server Integration Services (SSIS) to parse the XML export of the run history.

I will use a concrete example for this walkthrough. Recently I encountered a large number of errors while running an export profile against Active Directory. The specifics of the issue are not important for our discussion. The important part is that in order to correct the issue I had to write a script to and as an input for this script I needed the DNs of all the AD Accounts for which an error was raised during the export run execution. Hence the task is to extract all the DNs from the run profile history.

 

Saving run profile history as XML

1. Under the operations tab locate the profile run history in question.

2. Under actions click on Save to File.

Simplify the XML structure of the log

The idea here is to let SSIS parse the produced XML file and extract the interesting to us information (DNs). Unfortunately, the XML structure of the run profile log is too complex for SSIS to handle, for this reason we need to simplify the file by removing the nodes which are of no interest to us.

1. Open the XML log in XML Notepad.

2. Since the data of interest to us contains in the “synchronization-errors” node, let’s delete all other ones, and in doing so make the file consumable by SSIS.

This is how the file should look like once we removed the unnecessary (in our case) nodes.

 

Create SSIS Package to extract the DNs

1. Create new Integration Services Project

2. Add XML Source to the Data Flow Task design surface

3. Browse to the location where we saved the modified log.

4. Let SSIS generate XSD (XML schema), by clicking on Generate XSD.

5. Our XML source will have multiple outputs, therefore select “export-error”, since it contains the DN field.

In principal, we are almost done. At this point we can send the DNs into flat file output, SQL table, etc. Just as an option, I will show how to let SSIS build a PowerShell script to reset passwords for the accounts identified by the DNs we are getting from the log.

6. Drag “Derived Column” component into the design surface and wire it to the XML Source.

 

7. Create 3 derived columns:

• psCommand will contain the verb set-ADAccountPassword
• dn will contain the DN surrounded by single quotes, required to deal with the DNs containing spaces
• psParameter will contain the parameters for the set-ADaccountPassword

 

Please, not that I used set-ADAccountPassword strictly for demo purposes.

8. Now I want the generated commands to be placed on the Clipboard, as an example. I will achieve this by adding a Row Count component and attaching a Data Viewer in front of it.

Hint, to change the order of the columns in the Data Viewer, go into the properties and add columns in the order you need.

9. Run the package. Once the Data Viewer is displayed you can copy the data  and paste it into Notepad. Save the file with ps1 extension and the script is ready to run.