SQL Query to get the Maintenance Mode History for Particular Server SCOM 2016

SQL Query to get the Maintenance Mode History for Particular Server SCOM 2016

• Many time you get questions from Management whether the particular server was in Maintenance mode and how long.
• It has always been a challenge for SCOM engineer to pull the Data, as there is now easy way to pull this information.
• You can use the below SQL query to get that information.
• What this query does is that it queries the "OperationsManagerDW" database in "ManagedEntity"  table "MaintenanceMode" Table and "MaintenacenModeHistory" table where the ManagetEntity Row ID matches Maintenance mode Manage entity RowID and Maintenance Mode RowId matches Maintenance Mode Manage Entity Row ID.
• Replace the "Servername" with server name you wan the report for and you will get the Maintenance mode History report for the server

********************
USE OperationsManagerDW
SELECT ManagedEntity.DisplayName, MaintenanceModeHistory.*
FROM ManagedEntity WITH (NOLOCK) 
INNER JOIN
MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId 
INNER JOIN
MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId
where DisplayName Like '%servername%'

*******************