Introduction

In many cases, you want to have a better insight in the performance of your FIM Synchronization run profiles.

The FIM/MIM Sync interface only provides an instant overview of the tasks that recently run. Moreover, the availability of data is limited to the run history records available in the FIM/MIM sync database. If you want to have reports over a longer period of time, you need to export the data before cleaning the run history.

There are various options to generate reports, as discussed in the master article.

This article has the focus on building run history statistics with SQL and Excel Pivot Tables, to get a nice overview.


Prerequisites

For the procedure below you need

  • FIM Sync engine with sufficient run profile data
  • Permissions to run queries on the FIMSynchronization database
  • SQL query to pull data from the FIMSync database (download script here)
  • An Excel sheet to dump the query data (download a template sheet here)

 


Dependencies

None, except for the default, available FIM back-end components, you do not need extra tooling.

 


Data collection

 

 Caution

Although the SQL script below only reads information, the use of the script is at your own risk.

The FIM Services assume single and authoritative access to the databases.

When running a query against the FIM Databases, it is mandatory to use the 'with (nolock)' parameter to avoid any deadlocks on the FIM database tables. 

SQL

use FIMSynchronizationService
SELECT
 mms_management_agent.ma_name,
 mms_run_profile.run_profile_name,
 mms_run_history.run_number,
 mms_run_history.username,
 mms_run_history.is_run_complete,
 mms_run_history.run_result,
 mms_run_history.current_step_number,
 mms_run_history.total_steps,
 mms_run_history.start_date,
 mms_run_history.end_date,
 mms_run_history.mms_timestamp,
 mms_run_history.operation_bitmask
 
FROM
 mms_run_history WITH (nolock)
 
INNER JOIN
 mms_run_profile
ON
 mms_run_history.run_profile_id = mms_run_profile.run_profile_id
 
INNER JOIN
 mms_management_agent
ON
 mms_run_history.ma_id = mms_management_agent.ma_id
 
ORDER BY
 mms_run_history.mms_timestamp DESC

Run the query in the SQL Management studio, copy the table with results in the Excel sheet below.

 


Generating Statistics

Preparing the Excel sheet

Download the Excel sheet (download a template sheet here).

Rename the template to a name at your choice. 

SQL to Excel

Copy the data results of the SQL query into an Excel sheet.

Most FIM administrators work via an RDP connection, which supports to copy/paste information from the FIM console to an Excel sheet on the admin workstation.

Else, you save the results to file (or copy data to a txt or csv file), transfer the file to the admin workstation.

Then open the file with Excel and transfer the results to the sheet.


Downloads

SQL

Excel

 


See also