FIM 2010 & MIM 2016: Run profile statistics with SQL and Excel Pivot Tables
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