![\"ma_dashboard\"](\"http:\/\/blogs.kent.ac.uk\/unseenit\/files\/2016\/11\/MA_dashboard-1024x321.png\")
<\/p>\nIf, 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.<\/p>\n
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.<\/p>\n
For example:<\/p>\n
<\/p>\n
In the dashboard, I’ve squashed some of the less common error codes into other-error<\/em> 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 \ud83d\ude42 <\/p>\n <\/p>\n Important:<\/strong> 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.<\/p>\n The first view coalesces the various run steps into a run profile overview for each invocation:<\/p>\n 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 \ud83d\ude42<\/p>\n\r\nCREATE VIEW [MyDB].[dbo].[FIM_Run_History]\r\nAS\r\nSELECT rh.run_history_id \r\n ,rh.start_date \r\n ,ma.ma_name \r\n ,rh.is_run_complete \r\n ,rh.run_result \r\n ,rh.run_profile_name \r\n ,Max(sh.step_number) AS steps \r\n ,Sum(sh.stage_no_change) as sum_no_change\r\n ,Sum(sh.stage_add) as sum_stage_add \r\n ,Sum(sh.stage_update) as sum_stage_update \r\n ,Sum(sh.stage_rename) as sum_stage_rename \r\n ,Sum(sh.stage_delete) as sum_stage_delete \r\n ,Sum(sh.stage_deleteadd) as sum_stage_deleteadd \r\n ,Sum(sh.stage_failure) as sum_stage_failure \r\n ,Sum(sh.disconnector_filtered) as sum_disconnector_filtered \r\n ,Sum(sh.disconnector_joined_no_flow) as sum_disconnector_joined_no_flow \r\n ,Sum(sh.disconnector_joined_flow) as sum_disconnector_joined_flow \r\n ,Sum(sh.disconnector_joined_remove_mv) as sum_disconnector_joined_remove_mv \r\n ,Sum(sh.disconnector_projected_no_flow) as sum_disconnector_projected_no_flow \r\n ,Sum(sh.disconnector_projected_flow) as sum_disconnector_projected_flow \r\n ,Sum(sh.disconnector_projected_remove_mv) as sum_disconnector_projected_remove_mv \r\n ,Sum(sh.disconnector_remains) as sum_disconnector_remains \r\n ,Sum(sh.connector_filtered_remove_mv) as sum_connector_filtered_remove_mv \r\n ,Sum(sh.connector_filtered_leave_mv) as sum_connector_filtered_leave_mv \r\n ,Sum(sh.connector_flow) as sum_connector_flow \r\n ,Sum(sh.connector_flow_remove_mv) as sum_connector_flow_remove_mv \r\n ,Sum(sh.connector_no_flow) as sum_connector_no_flow \r\n ,Sum(sh.connector_delete_remove_mv) as sum_connector_delete_remove_mv \r\n ,Sum(sh.connector_delete_leave_mv) as sum_connector_delete_leave_mv \r\n ,Sum(sh.connector_delete_add_processed) as sum_connector_delete_add_processed \r\n ,Sum(sh.flow_failure) as sum_flow_failure \r\n ,Sum(sh.export_add) as sum_export_add \r\n ,Sum(sh.export_update) as sum_export_update \r\n ,Sum(sh.export_rename) as sum_export_rename \r\n ,Sum(sh.export_delete) as sum_export_delete \r\n ,Sum(sh.export_deleteadd) as sum_export_deleteadd \r\n ,Sum(sh.export_failure) as sum_export_failure \r\nFROM fimsynchronizationservice.dbo.mms_run_history rh WITH (nolock) \r\n JOIN fimsynchronizationservice.dbo.mms_run_profile rp WITH (nolock) \r\n ON rh.ma_id = rp.ma_id \r\n JOIN fimsynchronizationservice.dbo.mms_management_agent ma WITH (nolock) \r\n ON rp.ma_id = ma.ma_id \r\n LEFT JOIN fimsynchronizationservice.dbo.mms_step_history sh WITH (nolock) \r\n ON rh.run_history_id = sh.run_history_id \r\nGROUP BY rh.run_history_id \r\n ,rh.start_date \r\n ,ma.ma_name \r\n ,ma.ma_type \r\n ,rh.is_run_complete \r\n ,rh.run_result \r\n ,rh.run_profile_name \r\nGO\r\n<\/pre>\n
\r\nCREATE VIEW [MyDB].[dbo].[FIM_Run_Summary] AS\r\nSELECT ma_name, \r\n [completed-discovery-errors], \r\n [completed-export-errors], \r\n [completed-sync-errors], \r\n [completed-warnings], \r\n [no-start-ma], \r\n [stopped-extension-dll-exception], \r\n [stopped-parsing-errors], \r\n [success], \r\n [other-error] \r\nFROM (SELECT ma_name, \r\n CASE \r\n WHEN run_result = 'success' \r\n OR run_result = 'completed-discovery-errors' \r\n OR run_result = 'completed-export-errors' \r\n OR run_result = 'completed-sync-errors' \r\n OR run_result = 'completed-warnings' \r\n OR run_result = 'no-start-ma' \r\n OR run_result = 'stopped-extension-dll-exception' \r\n OR run_result = 'stopped-parsing-errors' THEN run_result \r\n ELSE 'other-error' \r\n END AS run_result, \r\n run_history_id \r\n FROM [MyDB].[dbo].[fim_run_history] \r\n WHERE is_run_complete = 1 \r\n AND Datediff(day, start_date, Getdate()) < 7) t \r\n PIVOT ( Count(run_history_id) \r\n FOR run_result IN ([completed-discovery-errors], \r\n [completed-export-errors], \r\n [completed-sync-errors], \r\n [completed-warnings], \r\n [no-start-ma], \r\n [stopped-extension-dll-exception], \r\n [stopped-parsing-errors], \r\n [success], \r\n [other-error]) ) p \r\n\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"