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%'

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

Powershell Script to put Multiple Computers in Maintenance mode in SCOM 2007 R2

Powershell Script to put Multiple Computers in Maintenance mode in SCOM 2007 R2

You can use the below script to add Mutiple Computer in Maintenance mode in SCOM 2007 R2

$comparr= get-content -file "c:\scomscripts\servert.txt"

$starttime = "12/25/2014 22:00" # Change the Starttime

$EndTime = "12/27/2014 23:00"   # Change the Endtime

foreach($Computer in #comparr)

{

$objComputer =Get-agent | where-object {$_.Name -match $computer

$objComputer.HostComputer | New-MaintenaceWindow -StartTime:$starttime -EndTime:$EndTime -Comment" "Server in MM mode"

}