Schedule SCOM Maintenance Mode with Orchestrator

Schedule SCOM Maintenance Mode with Orchestrator

  • Comments 4
  • Likes

Hello,

I was working with a customer when the need to schedule their maintenance was brought up.  As we all know this is a very sore spot for SCOM that we cannot do!  We had the great idea of using Orchestrator to do the dirty work for us as long as our customers filled out a CSV document (correctly :)). 

In this blog post I will show you how to build that Maintenance Mode from CSV File runbook and also a secondary runbook that will query your database every 5 minutes to initiate the scheduled maintenance.

High-Level Steps

1) Create Databases (one to store our scheduled Maintenance requests and the other for reporting).
2) Create File location that you will store the Template.csv and where your users will save their requests.
3) Create file location for the completed requests to be moved to.
4) Create MM from CSV file runbook (this can also be created to pull from SharePoint 2013)
5) Create the Schedule Maintenance Mode runbook
6) Test and release to your customers!

=================================================

Let’s start!

  1. We first need to create our 2 Databases.  You can always consolidate these into one but for my demo purposes I kept them separate.  I did use the same script to create the tables on both databases so the tables are mimics of each other.

Here is what my structure looks like:



Script to create the table(if you do use 2 databases, just change the USE [location]:
USE [ScheduledMaintMode]

GO

 /****** Object:  Table [dbo].[MMInfo]    Script Date: 4/21/2014 12:32:50 PM ******/

SET ANSI_NULLS ON
GO 
SET QUOTED_IDENTIFIER ON
GO 

CREATE TABLE [dbo].[MMInfo](
        [ServerName] [nvarchar](max) NOT NULL,
        [StartDate] [date] NOT NULL,
        [StartTime] [nvarchar](50) NOT NULL,
        [Duration] [nvarchar](50) NOT NULL,
        [RequestedBy] [nvarchar](max) NULL,
        [RepeatSchedule] [nchar](10) NULL,
        [NumberOfOccurences] [nchar](10) NULL,
        [CompletedOn] [nchar](10) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

2. Create our File locations.

The first location will be where we are going to store our MaintMode_Template.csv and where our users will also drop their requests.  We need to make sure and educate the users to open the template and SAVE AS MaintMode_[Their UserID].csv.  Now in my lab I just created this path local to my SCORCH server but you can create this on a file share that everyone will have access to.


3.  Now we need to create another location so that our completed files have a place for auditing.


You can see from the image above that we move and rename the file Month_Day_Year_FileName.  This allows us to see when it was run.
RUNBOOK #1: CSV FILE MM

Now we have done the first 2 steps so we need to open up SCORCH and create our Maintenance Mode folder.  Under this folder we will create 2 sub folders. 
One for the CSV File Maintenance Mode runbook and the other for Scheduled Maintenance Mode.

In our CSV file MM we need to right click and create new runbook.  When completed it will look like below:

So let’s dig in :) We start with our Monitor File Activity.  This is located under the File Management folder on the right side.


Monitor File:
Once you have this go ahead and drag onto our new Runbook.  Double click it and set the parameters to mimic below:

*We need to make sure that the relation in the filter says “Does not match pattern” and value = *Template*. 
In doing this, it will allow us to always keep our Template in the location without ever getting picked up by the runbook and moved.

Next we need to drag out the Run .Net Activity onto our screen so that we can parse the .csv data.  This activity is located under the System Activities.  To save time we will drag this icon on 5 times. ( it seems crazy but trust me ;) )


Once you have dragged all of them out make them look like our parent image and connect them all up.  To connect them on the right of the Monitor File activity in the runbook there will be a small arrow.  Hover over this until your cursor becomes a cross, then click and connect the activities.  This allows us to pull published data from anything connected in front of the selected activity.

Process Initial Data, we need to make sure the type is PowerShell (in fact all of the .Net Activities we use will be PowerShell).
You will notice that $filepath has some nasty value, this is because it is published data from Monitor File activity.
 
$FilePath= '\`d.T.~Ed/{A122FD91-6B43-4C55-978D-85142E54D074}.FileName\`d.T.~Ed/'
$Data = Import-CSV $FilePath
$Array = @()
ForEach ($agent in $Data)

{
 $ServerName = $agent.'ServerName(FQDN)'
 $Starttime = $agent.'StartTime(military & 5 min increments)'
 $StartDate = $agent.'StartDate(MM/DD/YYYY)'
 $Duration = $agent.'Duration(mins)'
$Repeat = $agent.'Repeat(Yes or No)'
$Type = $agent.'Type'
$Occurences = $agent.'Number Of Occurences'
$Owner = $agent.'Requested By'

     if($Starttime.ToLower() -ne 'now')

    {
        $RunNow = 'False'
    }
    Else
    {
        $RunNow = 'True'
    }

    $str = $ServerName +","+$StartDate+","+$Starttime+","+$Duration+","+$Repeat+","+$Type+","+$Occurences+","+$Owner+","+$RunNow
    $Array += $str+";"

}

 The image should look like this:


To reference published data from another activity, 1st they must be connected and 2nd you need to right click à subscribe à Published Data. This will by default pull the first connected activity, but just hit the drop down and you can pull from other activities as long as they are before the activity you’re on.  Also if you don’t see some common data, check the ‘Show common Published Data’(this is the data we will use to rename your file):



Once that is completed we need to publish some data from this activity that will be used down the road.



Now before we move to the next .Net Activity which is Agent Validation, we need to move our file and rename it:


These are located under the same folder as Monitor File.  We just need to drag them onto our runbook and connect them similar to above.  As for the parameters
Move the MM File:

Rename the File ( all I have done is right click the Destination properties and hit expand allowing for much easier editing/modifing):


Now let’s get back to Agent Validation Activity.  This is where we will validate the data the user entered is correct and also has a SCOM agent on it.  Here you use the Exchange integration pack to notify the User and SCOM team that there are boxes that are either incorrectly entered or don’t have a SCOM agent.
Agent Validation:


Script( you will need to modify the SCOM connection to your Mgmt. Server!!!):
#Import OM Module
Import-Module OperationsManager;
New-SCOMManagementGroupConnection [mgmt. server here]

$ValidArray = @()
$BadServerArray = @()

$Servers = '\`d.T.~Ed/{BB1A40F6-93D4-45FE-AB52-2A9DF8065A24}.Array\`d.T.~Ed/'
$Agents = $Servers.Split(';')
foreach ($line in $Agents)
{
If (![string]::IsNullOrEmpty($line))
    {
$line = $line.Trim()       
$processsplit = $line.split(',')
        $servername = $processsplit[0].Trim()
         $validAgent = (get-scomagent| where {$_.Displayname -eq $servername}).Displayname
        If (![string]::IsNullOrEmpty($servername))
           {
              if ($validAgent -match $servername)
                 {
                   $ValidArray += $line
                 }
         else
          {
                $BadServerArray += $line
          }
       }
    }
}

Publish Data:


Process Valid Agents, this where we will break apart our Good Array and use this data to either kick off immediately or schedule.
Details:


Downside is we now need to publish all of this :(

Published Data:


Nice!  Now the majority of the hard work is done!  Now we need to connect the Maintenance Mode activity from the SCOM IP. 
Locate this and drag onto the runbook and connect to Process Valid Agents. For this connector we need to add some criteria!

To do this we need to double click on the connecting line between the 2 activities and add our own criteria:


Start MM activity

*You need to make sure your Monitor field is like above. If not then Run Now MM will never work :(*  
Format:
{ServerName from “Process Valid Agents”} : Microsoft.Windows.Computer:{ServerName from “Process Valid Agents”}  -->NOTICE WHERE THERE ARE SPACES AND NOT!!!!

In my example I have this going to the Event Log, this is just another form of auditing you can apply.  Have SCOM alert so that you can report on it later when it finds MM events.

OK, now on to the one off scheduling.  This is just for I need Server A to go into Maintenance Mode next week and that is all.  We first need to get the Write to DB activity from Utilities.


Drag this icon onto the runbook twice and connect like below:


On the connector from Process Valid Agents to Schedule Maintenance Mode it needs to have this criteria in the Include:


Exclude:


We are doing to make sure that we are excluding our agents that need to have schedules setup. 


Schedule Maintenance Mode Activity:
Details:


Connection tab:

*You will need to modify the Server field to match where you created your DB(s) in the earlier steps.* 
**Also make sure that you gave your SCORCH service rights to the DB, because in the next runbook he will be creating, deleting, and moving records!**

Now let’s move to the recurring schedule portion of our runbook.  Here we are offering the users the option of daily, weekly, or monthly and in that last .Net Activity you will see the PowerShell we are using to accomplish this. First we need to start with the connector:
Include:

Exclude:


Create Schedule Activity:
Details:


Script:
$StartDate = '\`d.T.~Ed/{73B75251-B48E-45E8-B5D8-1B6DEC647D63}.StartDate\`d.T.~Ed/'
$strdate = get-date $StartDate
$Type = '\`d.T.~Ed/{73B75251-B48E-45E8-B5D8-1B6DEC647D63}.Type\`d.T.~Ed/'
$Occurences = '\`d.T.~Ed/{73B75251-B48E-45E8-B5D8-1B6DEC647D63}.Occurences\`d.T.~Ed/'
$Servername = '\`d.T.~Ed/{73B75251-B48E-45E8-B5D8-1B6DEC647D63}.Servername\`d.T.~Ed/'
$StartTime = '\`d.T.~Ed/{73B75251-B48E-45E8-B5D8-1B6DEC647D63}.StartTime\`d.T.~Ed/'
$Owner = '\`d.T.~Ed/{73B75251-B48E-45E8-B5D8-1B6DEC647D63}.Owner\`d.T.~Ed/'
$Duration = '\`d.T.~Ed/{73B75251-B48E-45E8-B5D8-1B6DEC647D63}.Duration\`d.T.~Ed/'

$array = @()

If($Type -eq 'Daily')
{
    $NewDate = $strdate
    if ($Occurences -gt '1')
    {
        $str = $Servername +","+($NewDate).ToShortDateString()+","+$Starttime+","+$Duration+","+$Type+","+$Occurences+","+$Owner
        $Array += $str +";"
        2..$Occurences | foreach-object{`
        $NewDate =$NewDate.AddDays(1)
        $Arraydate = $NewDate.ToShortDateString()
        $str = $Servername +","+$Arraydate+","+$Starttime+","+$Duration+","+$Type+","+$Occurences+","+$Owner
        $Array += $str +";"
        }
    }
}

If($Type -eq 'Weekly')
{
    $NewDate = $strdate
    if ($Occurences -gt '1')
    {
        $str = $Servername +","+($NewDate).ToShortDateString()+","+$Starttime+","+$Duration+","+$Type+","+$Occurences+","+$Owner
        $Array += $str +";"
        2..$Occurences | foreach-object{`
        $NewDate =$NewDate.AddDays(7)
        $Arraydate = $NewDate.ToShortDateString()
        $str = $Servername +","+$Arraydate+","+$Starttime+","+$Duration+","+$Type+","+$Occurences+","+$Owner
        $Array += $str +";"
        }
    }

If ($Type -eq 'Monthly')
{
$NewDate = $strdate
$DOW = $strdate.DayOfWeek
    if ($Occurences -gt '1')
    {
        $str = $Servername +","+($NewDate).ToShortDateString()+","+$Starttime+","+$Duration+","+$Type+","+$Occurences+","+$Owner
        $Array += $str +";"
        2..$Occurences | foreach-object{`
        $NewDate =$NewDate.AddMonths(1)
        if($NewDate.DayOfWeek -ne $strdate.DayOfWeek)
        {
            $n = 0
            do{
            $NDate = $NewDate.AddDays($n)
            $n++}
            Until ($NDate.DayOfWeek -eq $DOW)           

            if(($nDate.Day - $strdate.Day) -gt '5')
            {
                $n = 0
                do{
                $NDate = $NewDate.AddDays(-$n)
                $n++}
                Until ($NDate.DayOfWeek -eq $DOW)
            }
       }            

        $Arraydate = $NDate.ToShortDateString()      

        $str = $Servername +","+$Arraydate+","+$Starttime+","+$Duration+","+$Type+","+$Occurences+","+$Owner
        $Array += $str +";"
        }
    }
}

Published Data:

Process Scheduled Jobs Activity:
Details:


Script:
$process = '\`d.T.~Ed/{1B802AEB-4D49-4DFB-8C43-7FC61CE93400}.array\`d.T.~Ed/'
$processsplit = $process.split(',')
$servername = $processsplit[0]
$StartDate = $processsplit[1]
$StartTime = $processsplit[2]
$Duration = $processsplit[3]
$Type = $processsplit[4]
$Occurences = $processsplit[5]
$Owner = $processsplit[6]
if ($Owner -match ";")
{
$Owner = $Owner.Replace(";","")
}

Published Data:


Now the very last activity of the CSV File MM runbook, :)!!!

Schedule Future MM Activity:

Details:


Connection tab:

*Make sure to modify for your SQL Server from the first step.*


RUNBOOK #2: SCHEDULED MAINT. MODE
Overview of runbook:


For this runbook we will run on a 5 minute interval as we don’t want to overwhelm our SCORCH instance and also the SCOM instance.  We need to start with the Monitor Date/Time Activity. This activity will be located under Scheduling.

5 Min. Interval Activity:


We now need to get the Query Database activity under the same location as the Write to Database activity.  Drag this activity to your runbook and connect the 2 activities.
We will also need to drag 3 more of them for activities like you see in the main screen shot.

Query Database Activity:
Details:


Query:
select ServerName, Duration, StartTime,StartDate,RequestedBy from MMInfo
where StartDate like (SELECT FORMAT(CURRENT_TIMESTAMP, 'yyyy-MM-dd'))
and StartTime Between (SELECT FORMAT(CURRENT_TIMESTAMP, 'HH:mm')) and DATEADD(MINUTE, 5, (SELECT FORMAT(CURRENT_TIMESTAMP, 'HH:mm')))

Connection tab:

*Be sure to adjust for your SQL Server

Next we need to drag a Run .Net Activity to our runbook and connect it from the Query Database activity. We are also going to be adding some criteria to the connector to stop the run book if no rows are returned from our query.


Process Query Results Activity:
Details:


Script:
$process = '\`d.T.~Ed/{6BEF2F48-9A68-41D1-A1C1-E3A16B6C10A5}.Full-line\`d.T.~Ed/'
$processsplit = $process.split(';')
$servername = $processsplit[0]
$Duration = $processsplit[1]
$StartTime = $processsplit[2]
$StartDate = $processsplit[3]
$RequestedBy = $processsplit[4]

 
Published Data:


Now we have broken apart all the data from our query and we need to not only move it to our completed from scheduled db but also kick off Maintenance Mode!!!  Kind of the whole reason you’re hereJ!!!

Start Maintenance Mode Activity:
Details:

*Once again just like in the other runbook you need to be very cautious of the Monitor field.  If this isn’t right you will never start MM on your agents.
**You will also see two other activities coming off of Start Maintenance Mode, these are just extra steps for notifying that MM has started and auditing.

Update MM Tables Activity:
This activity will be used to move the selected rows from Scheduled to Completed DBs.
Details:

*If you used only one DB for your tables make sure you account for that in our activities!

Script:
Insert into [CompletedMaintMode].[dbo].[MMInfo]
Select *from MMInfo
where StartDate = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.StartDate\`d.T.~Ed/'
and StartTime = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.StartTime\`d.T.~Ed/'
and ServerName = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.servername\`d.T.~Ed/'
and Duration = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.Duration\`d.T.~Ed/'
and RequestedBy = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.RequestedBy\`d.T.~Ed/'

Connection Tab:


We need to move onto the Update Completed.  This activity will update our rows notifying us for our reports when the MM was actually executed.

Update Completed Activity:
Details:


Script:
Update [CompletedMaintMode].[dbo].[MMInfo]
set [CompletedMaintMode].[dbo].[MMInfo].CompletedOn = (SELECT FORMAT(CURRENT_TIMESTAMP, 'yyyy-MM-dd'))
where StartDate = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.StartDate\`d.T.~Ed/'
and StartTime = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.StartTime\`d.T.~Ed/'
and ServerName = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.servername\`d.T.~Ed/'
and Duration = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.Duration\`d.T.~Ed/'
and RequestedBy = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.RequestedBy\`d.T.~Ed/'

Connection:


Now that we have updated our completed we can now delete all completed records from our Scheduled DB.  This will keep it clean and hopefully minimize any confusion for users, reporting, etc.

Delete from ScheduledMM Activity:
Details:


Script:
Delete from MMInfo
where StartDate = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.StartDate\`d.T.~Ed/'
and StartTime = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.StartTime\`d.T.~Ed/'
and ServerName = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.servername\`d.T.~Ed/'
and Duration = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.Duration\`d.T.~Ed/'
and RequestedBy = '\`d.T.~Ed/{EF99E84D-DEEE-442F-8B11-C468131532E9}.RequestedBy\`d.T.~Ed/'

Connection:



7. Validation time!!!

Create my file:

Drop onto my scheduled folder and watch the completed to see it working :)



Now I look back at the runbook and I see happiness:


Now let’s check the database to see our schedule!

I will modify one of the rows to be today and start at 3:58PM

Verify that that my server isn’t in Maintenance Mode before job kicks off:


Back to the Scheduled Maint. Mode runbook and watch for the 3:55 job run and pick it up.

 
So we see it was picked up from the 3:55 job so let’s check the DB again.

Only 3 rows!!

Now we can check the Completed table:
  

Now back to SCOM to validate!!!
As you can see the time difference is 40 minutes and the comment matches exactly what we have for our Maintenance Mode activities:

Now this is just the Ford Pinto of how to schedule maintenance mode, you can always use Tim McFadden's Cadillac version which is VERY nice!!!

I am always open to hearing about how you do it at your company or if you think there are ways to improve this! 
Please leave comments on either, I would love to read them.

Disclaimer:
This example is provided “AS IS” with no warranty expressed or implied. Run at your own risk.
The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft.

**Always test in your lab first**  Do this at your own risk!! The author will not be held responsible for any damage you incur when making these changes!

Attachment: MaintenanceModeTemplate.csv
Comments
  • **Note: This is for SCOM 2012...Not 2007 R2**

  • This. Is. Epic. Awesome job mate!!!

  • Awesome! Boom goes the Dynamite!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment