Anyone working in a fairly large organisation will be familiar with the frustrations experienced when people don't manage maintenance mode correctly. This results in machines staying in maintenance mode way longer than needed, or the machines coming out before the work is completed. This results in severe alert storms and really messes up your alert statistics.
I developed a clever little SQL script which will get all machines in maintenance mode and send a reminder when the machine has breached a "reminder" threshold.
There are a few steps needed here and I'm not going to go through all of them. This script was customised for our environment but hey, it may help you in some way. You will however need the following:
1) A SQL Linked Server connection to Active Directory - this allows us to lookup the email address of the AD user who put the machine into maintenance mode. In our company we make use of a centralised server details page (with MM integration). If this page is used to put the machine into maintenance mode, another "service" account is used, but the username is stored in the comments section as follows: DOMAIN\USER: Comments
2) SQL Mail will need to be setup.
3) There is another portion to this - a Web Service which receives the request to extend the maintenance mode. You may choose to omit this portion but it's nice functionality to have. This is in C#.
SQL Code:
DECLARE @now datetime;
DECLARE @LocalGMTOffset int;
DECLARE @MMWindowAlertThresholdPerc int;
DECLARE @startPos int;
DECLARE @endPos int;
DECLARE @userName varchar(200);
DECLARE @sql nvarchar(4000);
DECLARE @AD varchar(200);
DECLARE @WebServer varchar(200);
DECLARE @actionAccount varchar(200);
SET @LocalGMTOffset = +2
SET @MMWindowAlertThresholdPerc = 75;
SET @NOW = dateadd(hour,(@LocalGMTOffset * -1),getdate());
SET @AD = 'LDAP://AD';
SET @WebServer = 'WebServer';
SET @actionAccount = 'DOMAIN\ServerDetailUser';
-- Remove this when live - need it for testing because temp mails are only cleared on disconnection
--drop table #tempMailsToSend
--- Get all records needing to be mailed
INSERT [monitor].[dbo].[tb_MMWindows] SELECT bme.BaseManagedEntityId, mm.StartTime, mm.ScheduledEndTime, 0
FROM maintenancemode mm
INNER JOIN BaseManagedEntity bme ON (mm.BaseManagedEntityId = bme.BaseManagedEntityId)
LEFT JOIN [monitor].[dbo].[tb_MMWindows] MMW on MMW.BaseManagedEntityId = mm.BaseManagedEntityId
WHERE mm.EndTime IS NULL AND mm.IsInMaintenanceMode = 1 AND mmw.ack IS NULL AND bme.isDeleted = 0 AND
((round(((convert(float, DATEDIFF(minute,mm.StartTime,@NOW)) / convert(float,DATEDIFF(minute,mm.StartTime,mm.ScheduledEndTime)))*100),0)) > @MMWindowAlertThresholdPerc)
ORDER BY mm.ScheduledEndTime DESC
-- Filter only on the Top Level IDs
SELECT identity(int,1,1) as id, a.* INTO #tempMailsToSend FROM (SELECT count(*) as childitems, mm.StartTime, mm.ScheduledEndTime, bme2.DisplayName, bme2.BaseManagedEntityId,
CASE WHEN([MM].[User] LIKE @actionAccount + '%') THEN
SUBSTRING(SUBSTRING(LTRIM(Comments),0,CHARINDEX(':',LTRIM(Comments))),CHARINDEX('\',Comments)+1,LEN(Comments))
ELSE SUBSTRING(LTRIM([MM].[User]),CHARINDEX('\',[MM].[User])+1,LEN([MM].[User])) END as UserName, mm.Comments
FROM [monitor].[dbo].[tb_MMWindows] MMW
INNER JOIN MaintenanceMode MM ON (MM.BaseManagedEntityId = MMW.BaseManagedEntityId)
INNER JOIN BaseManagedEntity bme ON (mm.BaseManagedEntityId = bme.BaseManagedEntityId)
INNER JOIN BaseManagedEntity bme2 ON (bme.ToplevelHostEntityId = bme2.BaseManagedEntityId)
WHERE MMW.ack=0 AND bme2.DisplayName != 'Microsoft.SystemCenter.AgentWatchersGroup' AND MMW.ack = 0
GROUP BY bme2.TopLevelHostEntityId, MM.Comments, [MM].[User], bme2.DisplayName, mm.StartTime, mm.ScheduledEndTime, bme2.BaseManagedEntityId) a
--SELECT * FROM #tempMailsToSend
--NEED THIS UPDATE HERE
UPDATE [monitor].[dbo].[tb_MMWindows] SET ack=1
SELECT @startPos = 1, @endPos = count(*) FROM #tempMailsToSend
WHILE @startPos <= @endPos
BEGIN
DECLARE @bodyVal nvarchar(4000);
DECLARE @subjectVal varchar(255);
DECLARE @mailBody nvarchar(500);
DECLARE @DisplayName varchar(255);
DECLARE @StartTime datetime;
DECLARE @ScheduledEndTime datetime;
DECLARE @Comments nvarchar(1000);
DECLARE @ChildItems int;
DECLARE @BmeId varchar(100);
DECLARE @mail varchar(200);
SET @sql = 'SELECT @mail = mail FROM OPENQUERY( ADSI, ''SELECT mail FROM '''''+ @AD + '''''
WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''' AND SAMAccountName = '''''+(SELECT UserName FROM #tempMailsToSend WHERE id=@startPos)+''''''')'
EXEC sp_executesql
@query = @sql,
@params = N'@mail varchar(200) OUTPUT',
@mail = @mail OUTPUT
SELECT @BmeId = BaseManagedEntityId, @DisplayName = DisplayName,@StartTime = StartTime, @ScheduledEndTime = ScheduledEndTime, @Comments = Comments FROM #tempMailsToSend WHERE id=@startPos
IF @Comments IS NULL
BEGIN
SET @Comments = ''
END
SET @subjectVal = 'MM Reminder: ' + @DisplayName
SET @bodyVal = '
<HTML>
<HEAD>
<TITLE>Maintenance Mode Reminder</TITLE>
<BODY>
<style>
body {
background-color:#ECECEC;
font-family: Arial, Helvetica, sans-serif;
font-size: 14px;
}
td {
background-color:#ECECEC;
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
h1 {
margin:0px 0px 5px 0px;
font-size: 16px;
}
table td {
text-align: center;
background-color: #fff;
font-size: 12px;
}
table th{
text-align: center;
background-color: #000;
color: #fff;
font-size: 12px;
}
.button {
display: inline-block;
width: 80px !important;
background-color: #ECECEC;
padding: 2px;
text-align: center;
color: #000;
text-decoration: none;
border: 1px solid #000;
}
.button:hover {
background-color: #D6D5C3;
}
</style>
</HEAD>
<BODY>
<h1>SCOM Automated Maintenance Mode Reminder</h1>
Hi,'+ '<br />
<br />
Please note that the machine listed below is approaching the end of its maintenance window. It is currently passed the set threshold of <strong>' + CAST(@MMWindowAlertThresholdPerc as varchar(2)) + '% total time elapsed.'+ '</strong><br /><br />
<table><tr>
<th>DisplayName</th><th>Start Time</th><th>Scheduled End Time</th><th>Comments</th></tr>
<tr><td>'+@DisplayName + '</td><td>' +CAST(dateadd(hour,@LocalGMTOffset,@StartTime) as nvarchar(50)) + '</td><td>' +CAST(dateadd(hour,@LocalGMTOffset,@ScheduledEndTime) as nvarchar(50))+ '</td><td>' +@Comments + '</td></tr>
</table>
<br /><br /><br />
Extend by:<br /><br />
<a href="http://' + @WebServer + '/mm/Default.aspx?bmid='+ @BmeId + '&extend=1&DisplayName='+@DisplayName+'" class="button">1 hour</a>
<a href="http://' + @WebServer + '/mm/Default.aspx?bmid='+ @BmeId + '&extend=2&DisplayName='+@DisplayName+'" class="button">2 hours</a>
<a href="http://' + @WebServer + '/mm/Default.aspx?bmid='+ @BmeId + '&extend=4&DisplayName='+@DisplayName+'" class="button">4 hours</a>
<a href="http://' + @WebServer + '/mm/Default.aspx?bmid='+ @BmeId + '&extend=8&DisplayName='+@DisplayName+'" class="button">8 hours</a><br />
<br /><br />
Other options:<br /><br />
<a href="http://' + @WebServer + '/mm/Default.aspx?bmid='+ @BmeId + '&extend=0&DisplayName='+@DisplayName+'" class="button">Take out of maintenance mode</a><br />
</BODY>
</HTML>'
EXEC msdb.dbo.sp_send_dbmail @recipients=@mail,
@subject = @subjectVal,
@body = @bodyVal,
@body_format = 'HTML';
SET @startPos = @startPos + 1
END
--MUST REMOVE THIS WHEN LIVE - this is left here for testing purposes so it'll always send
--delete from [monitor].[dbo].[tb_MMWindows]
---------------------------
C# Web Service Code:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Microsoft.EnterpriseManagement;
using Microsoft.EnterpriseManagement.Configuration;
using Microsoft.EnterpriseManagement.Monitoring;
using System.Collections.ObjectModel;
using System.Collections.Generic;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int gmtOffset = 2;
string bmid = Request.QueryString["bmid"];
int extendHours = System.Convert.ToInt32(Request.QueryString["extend"]);
string pcDisplayName = Request.QueryString["displayname"];
if (bmid != null && pcDisplayName != null)
{
ManagementGroup mg = new ManagementGroup("RMS1");
string mcCriteria = "Name = 'Microsoft.Windows.Computer'";
string query = "Id = '" + bmid + "'";
MonitoringClassCriteria criteria = new MonitoringClassCriteria(mcCriteria);
MonitoringClass monClass = mg.GetMonitoringClasses(criteria)[0];
MonitoringObjectCriteria objCriteria = new MonitoringObjectCriteria(query, monClass);
List monObjects = new List(mg.GetMonitoringObjects(objCriteria));
if (monObjects.Count == 0)
{
litOutput.Text = "Could not find an object with that display name. System Center has been notified.";
}
foreach (MonitoringObject monObject in monObjects)
{
if (extendHours == 0)
{
DateTime scheduledEndTime = DateTime.UtcNow;
try
{
monObject.StopMaintenanceMode(scheduledEndTime, Microsoft.EnterpriseManagement.Common.TraversalDepth.Recursive);
litOutput.Text = pcDisplayName + " has successfully been taken out of maintenance mode.";
}
catch(Exception Ex)
{
litOutput.Text = "Encountered an error stopping maintenance mode. System Center has been notified.
" + Ex.Message;
}
}
else
{
if (!monObject.InMaintenanceMode)
{
try
{
DateTime startTime = DateTime.UtcNow;
DateTime scheduledEndTime = DateTime.UtcNow.AddHours(extendHours);
string comments = extendHours + " hour maintenance mode window requested";
monObject.ScheduleMaintenanceMode(startTime, scheduledEndTime, 0, comments, Microsoft.EnterpriseManagement.Common.TraversalDepth.Recursive);
litOutput.Text = pcDisplayName + " has already been taken out of maintenance mode. Starting a new maintenance mode window for " + extendHours + " hours." +
"
Scheduled end time is: " + scheduledEndTime.AddHours(gmtOffset).ToString();
}
catch(Exception Ex)
{
litOutput.Text = "Encountered an error placing machine into maintenance mode. System Center has been notified.
" + Ex.Message;
}
}
else
{
try
{
MaintenanceWindow myWindow = monObject.GetMaintenanceWindow();
DateTime scheduledEndTime = myWindow.ScheduledEndTime.ToUniversalTime().AddHours((extendHours + gmtOffset));
string updatedComments = myWindow.Comments + " || " + extendHours + " hour extension requested";
monObject.UpdateMaintenanceMode(scheduledEndTime, 0, updatedComments, Microsoft.EnterpriseManagement.Common.TraversalDepth.Recursive);
litOutput.Text = pcDisplayName + " has had its maintenance mode extended by the requested " + extendHours + " hours" +
"
New scheduled end time is: " + scheduledEndTime.AddHours(gmtOffset).ToString();
}
catch(Exception Ex)
{
litOutput.Text = "Encountered an error extending maintenance mode. System Center has been notified.
" + Ex.Message;
}
}
}
}
}
}
}
And that's it... this works nicely once it's setup. It may take you a while to get all the components right - if you need help give me a shout.
I have found that SSRS 2008 is great for creating dashboards so we can view what is happening in SCOM at all times. We have mounted 4 23” monitors with dashboards that can cycle pages to help us know what is going on in our SCOM environment at all times. The dashboards run against the warehouse and are pretty much real time. 
