FIM Run History Reporting

If, like me, you have frequent jobs in your FIM (or MIM!) instance then the Operations tab can get a bit unwieldy and spotting issues in less frequent jobs can be hard.

FIM stores all the data you need to get a small dashboard of recent job results in its database and the two SQL views below provide a pivot table of MA with the count of jobs per result.

For example:

In the dashboard, I’ve squashed some of the less common error codes into other-error for clarity. The real code is available in the first view. If you particularly want to expose another result then you can probably work that out for yourself 🙂

Important: Do not modify the schema of your FIMSyhnchronizationService database. Do this in a separate schema! The example below uses MyDB which you’ll need to create.

The first view coalesces the various run steps into a run profile overview for each invocation:

CREATE VIEW [MyDB].[dbo].[FIM_Run_History]
AS
SELECT rh.run_history_id 
       ,rh.start_date 
       ,ma.ma_name  
       ,rh.is_run_complete 
       ,rh.run_result 
       ,rh.run_profile_name 
       ,Max(sh.step_number) AS steps 
       ,Sum(sh.stage_no_change) as sum_no_change
       ,Sum(sh.stage_add) as sum_stage_add 
       ,Sum(sh.stage_update) as sum_stage_update 
       ,Sum(sh.stage_rename) as sum_stage_rename 
       ,Sum(sh.stage_delete) as sum_stage_delete 
       ,Sum(sh.stage_deleteadd) as sum_stage_deleteadd 
       ,Sum(sh.stage_failure) as sum_stage_failure 
       ,Sum(sh.disconnector_filtered) as sum_disconnector_filtered 
       ,Sum(sh.disconnector_joined_no_flow) as sum_disconnector_joined_no_flow 
       ,Sum(sh.disconnector_joined_flow) as sum_disconnector_joined_flow 
       ,Sum(sh.disconnector_joined_remove_mv) as sum_disconnector_joined_remove_mv 
       ,Sum(sh.disconnector_projected_no_flow) as sum_disconnector_projected_no_flow 
       ,Sum(sh.disconnector_projected_flow) as sum_disconnector_projected_flow 
       ,Sum(sh.disconnector_projected_remove_mv) as sum_disconnector_projected_remove_mv 
       ,Sum(sh.disconnector_remains) as sum_disconnector_remains 
       ,Sum(sh.connector_filtered_remove_mv) as sum_connector_filtered_remove_mv 
       ,Sum(sh.connector_filtered_leave_mv) as sum_connector_filtered_leave_mv 
       ,Sum(sh.connector_flow) as sum_connector_flow 
       ,Sum(sh.connector_flow_remove_mv) as sum_connector_flow_remove_mv 
       ,Sum(sh.connector_no_flow) as sum_connector_no_flow 
       ,Sum(sh.connector_delete_remove_mv) as sum_connector_delete_remove_mv 
       ,Sum(sh.connector_delete_leave_mv) as sum_connector_delete_leave_mv 
       ,Sum(sh.connector_delete_add_processed) as sum_connector_delete_add_processed 
       ,Sum(sh.flow_failure) as sum_flow_failure 
       ,Sum(sh.export_add) as sum_export_add 
       ,Sum(sh.export_update) as sum_export_update 
       ,Sum(sh.export_rename) as sum_export_rename 
       ,Sum(sh.export_delete) as sum_export_delete 
       ,Sum(sh.export_deleteadd) as sum_export_deleteadd 
       ,Sum(sh.export_failure) as sum_export_failure 
FROM   fimsynchronizationservice.dbo.mms_run_history rh WITH (nolock) 
       JOIN fimsynchronizationservice.dbo.mms_run_profile rp WITH (nolock) 
         ON rh.ma_id = rp.ma_id 
       JOIN fimsynchronizationservice.dbo.mms_management_agent ma WITH (nolock) 
         ON rp.ma_id = ma.ma_id 
       LEFT JOIN fimsynchronizationservice.dbo.mms_step_history sh WITH (nolock) 
              ON rh.run_history_id = sh.run_history_id 
GROUP BY rh.run_history_id 
          ,rh.start_date 
          ,ma.ma_name 
          ,ma.ma_type 
          ,rh.is_run_complete 
          ,rh.run_result 
          ,rh.run_profile_name 
GO

The second view creates a Pivot Table (like in Excel) of the data from the first view grouping by MA and result. I’ve limited it to the last 7 days but you can tweak that 🙂

CREATE VIEW [MyDB].[dbo].[FIM_Run_Summary] AS
SELECT ma_name, 
       [completed-discovery-errors], 
       [completed-export-errors], 
       [completed-sync-errors], 
       [completed-warnings], 
       [no-start-ma], 
       [stopped-extension-dll-exception], 
       [stopped-parsing-errors], 
       [success], 
       [other-error] 
FROM   (SELECT ma_name, 
               CASE 
                 WHEN run_result = 'success' 
                       OR run_result = 'completed-discovery-errors' 
                       OR run_result = 'completed-export-errors' 
                       OR run_result = 'completed-sync-errors' 
                       OR run_result = 'completed-warnings' 
                       OR run_result = 'no-start-ma' 
                       OR run_result = 'stopped-extension-dll-exception' 
                       OR run_result = 'stopped-parsing-errors' THEN run_result 
                 ELSE 'other-error' 
               END AS run_result, 
               run_history_id 
        FROM   [MyDB].[dbo].[fim_run_history] 
        WHERE  is_run_complete = 1 
               AND Datediff(day, start_date, Getdate()) < 7) t 
       PIVOT ( Count(run_history_id) 
             FOR run_result IN ([completed-discovery-errors], 
                                [completed-export-errors], 
                                [completed-sync-errors], 
                                [completed-warnings], 
                                [no-start-ma], 
                                [stopped-extension-dll-exception], 
                                [stopped-parsing-errors], 
                                [success], 
                                [other-error]) ) p 

GO