Scenario :
- On our company third-party CMDB Management Portal, people could register interventions on their servers, scheduling downtimes on their servers in SCOM, so it won't impact on KPI or Uptime.
Solution :
- Created a small database with a simple table.
- Created a PS1 that reads data from the above table and for every record that is not 'Processed' it schedules its maintenance on SCOM.
1) Create the database on your MSSQL Server and the table with the following schema :
CREATE TABLE Scheduling (
ID int IDENTITY(1,1) PRIMARY KEY,
CI varchar(255),
Instance varchar(255),
Type varchar(255) NOT NULL,
Team varchar(255) NOT NULL,
StartTime SMALLDATETIME NOT NULL,
EndTime SMALLDATETIME NOT NULL,
Comment varchar(255) NOT NULL,
Status varchar(255) NOT NULL,
);
2) Create two Runbooks like this :
2.1 ) InsertIntoDB
- Basicaly used for the Third-Party software to consume it as web-service so it can make new inserts in your MSSQL table.
$SQLServer = "" #Your Server
$SQLDBName = "" #YourDBName
$connString = "Data Source=$SQLServer;Initial Catalog=$SQLDBName;Integrated Security = True"
$connection = New-Object System.Data.SqlClient.SqlConnection($connString)
$connection.Open()
$sqlcmd = $connection.CreateCommand()
# This Strange Characters will disapear when pasting it into Orchestrator :)
$SqlQuery = "INSERT INTO Scheduling (ci,citype,Team,StartTime,EndTime,Comment,Status) VALUES ('\`d.T.~Ed/{B65C878B-BF92-4D90-AEA1-E8E03B2813D6}.{27788CF1-0A35-47D4-AA0B-B06325A5C5D4}\`d.T.~Ed/','\`d.T.~Ed/{B65C878B-BF92-4D90-AEA1-E8E03B2813D6}.{B0B8E6F4-2245-4FA6-BB99-D626CCA011E7}\`d.T.~Ed/','\`d.T.~Ed/{B65C878B-BF92-4D90-AEA1-E8E03B2813D6}.{7E4E2E7D-6070-472F-AB9A-7331E13CD6CF}\`d.T.~Ed/','\`d.T.~Ed/{B65C878B-BF92-4D90-AEA1-E8E03B2813D6}.{35128BA4-9FB4-417F-BC9C-FE4F43EC884C}\`d.T.~Ed/','\`d.T.~Ed/{B65C878B-BF92-4D90-AEA1-E8E03B2813D6}.{6B2977E6-609B-4139-A3FD-CF7646C27C10}\`d.T.~Ed/','\`d.T.~Ed/{B65C878B-BF92-4D90-AEA1-E8E03B2813D6}.{B7CD14CD-6990-4ECD-9CAA-A5EDF9C6D73D}\`d.T.~Ed/','NOT PROCESSED');"
$sqlcmd.CommandText = $SqlQuery
$sqlcmd.CommandText = $SqlQuery
$results = $sqlcmd.ExecuteNonQuery()
2.2 ) OpsMgr MM
- Used to periodically put objects into maintenance.
- It selects unprocessed items with specific conditions.
- When processed it updates table with processed status so it wont process anymore.
(Note) - I've got this small PS1 script before i start doing things - it's simple test connectivity to the database from the runbook server :
If ( (new-object System.Net.Sockets.TcpClient("\`d.T.~Vb/{4D50CD67-31C0-48AA-A37E-2E017F6DC9F9}\`d.T.~Vb/","1433")).connected -eq $true ) {
$LOG = "DATABASE CONNECTION OK" } Else { $LOG = "DATABASE CONNECTION NOT OK" }
This is the one that does the magic!
try{
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.EnterpriseManagement.OperationsManager.Common") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.EnterpriseManagement.Core') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.EnterpriseManagement.OperationsManager') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.EnterpriseManagement.Runtime') | Out-Null
} Catch { "" }
try{
$script:MGConnSetting = New-Object Microsoft.EnterpriseManagement.ManagementGroupConnectionSettings('YOUR_OPS_MGR_SERVER')
$script:MG = New-Object Microsoft.EnterpriseManagement.ManagementGroup($MGConnSetting)
} Catch {
$Script:log += "Cannot connect to OpsMgr "
exit
}
$script:SQLServer = "MyMSSQLServer"
$script:SQLDBName = "DatabaseName"
$script:connString = "Data Source=$SQLServer;Initial Catalog=$SQLDBName;Integrated Security = True"
$script:connection = New-Object System.Data.SqlClient.SqlConnection($connString)
$script:Reason = [Microsoft.EnterpriseManagement.Monitoring.MaintenanceModeReason]::PlannedOther
$script:Transversal = [Microsoft.EnterpriseManagement.Common.TraversalDepth]::Recursive
$Script:log = "--------"
$script:class = ""
$ClassInstance = ""
$table = ""
Function SqlDataManagement {
param($QueryType)
Try {
$connection.Open()
} Catch {
$Script:log += "Cannot Open DB Connection"
exit
} # Open Database Connection
$sqlcmd = $connection.CreateCommand()
If ($QueryType -eq 'update') {
$SqlQuery = "UPDATE Scheduling SET Status = 'Processed' WHERE ID = $ID"
$sqlcmd.CommandText = $SqlQuery
$results = $sqlcmd.ExecuteNonQuery()
} # Update Database
If ($QueryType -eq 'update_not_found') {
$SqlQuery = "UPDATE Scheduling SET Status = 'CI NOT FOUND' WHERE ID = $ID"
$sqlcmd.CommandText = $SqlQuery
$results = $sqlcmd.ExecuteNonQuery()
} # Update Database
If ($QueryType -eq 'select') {
$SqlQuery = "SELECT * FROM Scheduling WHERE Status = 'Not Processed' AND DATEDIFF(MINUTE,GETDATE(),[StartTime]) BETWEEN -1 AND 0 AND DATEDIFF(MINUTE,[StartTime],[EndTime]) > 5"
$sqlcmd.CommandText = $SqlQuery
$results = $sqlcmd.ExecuteReader()
$script:table = new-object “System.Data.DataTable”
$script:table.Load($results)
} # Select rows to manage
$connection.Close()
}
Function Get-SCOMObjectbyClass([string]$ClassDisplayName,[string]$CI) {
$script:ClassCriteria = New-Object Microsoft.EnterpriseManagement.Configuration.MonitoringClassCriteria("Name = '$ClassDisplayName'")
$script:MonitoringClass = $MG.GetMonitoringClasses($ClassCriteria)
$script:MOCriteria = New-Object Microsoft.EnterpriseManagement.Monitoring.MonitoringObjectGenericCriteria("DisplayName LIKE '$CI%'")
Try {
$script:ClassInstance = ($MG.GetMonitoringObjects($MOCriteria, $MonitoringClass[0]))[0]
} Catch {
$Script:log += "$CI not found or not belonging to $ClassDisplayName"
}
}
Function Send-Email([string]$Status) { #Mail & HTML Stuff
$Head = ""
$Image = "C:\OpsMgr\MM\images\logo_detail.png"
$att1 = new-object Net.Mail.Attachment($Image)
$att1.ContentType.MediaType = “image/png”
$att1.ContentId = “Attachment”
$att1.ContentDisposition.Inline = $true
$att1.ContentDisposition.DispositionType = “Inline”
$body = "<img src='cid:Attachment' height='12%' width='12%'/><br/>"
$body += "<center><h5 style=color:#999999>SCOM - Schedule Maintenance Mode</center></h5>"
If ( $Status -eq "OK" ) {
$body += "CI - $CI
"
$body += "Inicio - $StartTime
"
$body += "Fim - $EndTime
"
$body += "Razão - $Comment
"
} ElseIf ( $Status -eq "NOT OK" ) {
$body += "CI - $CI
"
$body += ""
$body += "Putting $CI in MM failed"
$body += "Reason:
"
$body += ""
$body += "$LOG"
}
$smtpServer = "SMTP.SERVER"
$smtpFrom = "FROM@ADDRESS.COM"
$smtpTo = "TO@ADDRESS.COM"
$messageSubject = "SCOM-ScheduleMaintenanceMode - $CI"
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true
$message.Attachments.Add($att1)
$message.Body = ConvertTo-Html -Body $body -Head $head
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
}
SqlDataManagement -QueryType select
foreach ( $i in $table ) {
$script:StartTime = (Get-Date -date ($i.StartTime).ToString()).ToUniversalTime()
$script:EndTime = ($StartTime.AddMinutes(($i.EndTime - $StartTime).TotalMinutes)).ToUniversalTime()
$script:Comment = $i.Comment
$script:ID = $i.ID
$script:Team = $i.Team
$script:Type = $i.Type
$script:CI = $i.CI
switch ( $Type ) {
"NetworkDevice" { $script:Class = 'System.NetworkManagement.Node' }
"Computer" { $script:Class = 'System.Computer' }
} # Switch to check which object class type it is | Add many as you may like or need.
Get-SCOMObjectbyClass -ClassDisplayName "$script:Class" -CI $script:CI
If ( $ClassInstance -ne $null -and ($ClassInstance.InMaintenanceMode) -ne $true ) {
try {
$ClassInstance.ScheduleMaintenanceMode($StartTime,$EndTime,$Reason,$Comment,$Transversal)
$ClassInstance = ($MG.GetMonitoringObjects($MOCriteria, $MonitoringClass[0]))[0]
If ( $ClassInstance.InMaintenanceMode -eq $true ) {
SqlDataManagement -QueryType update
Send-Email -Status "OK"
}
Else { $Script:log += = "Failed to put $CI in MM."
Send-Email -Status "NOT OK"
}
} # Object in Maintenance Mode
Catch {
$Script:log += "Exception while putting $CI in MM :" + "$_.Exception.Message"
Send-Email -Status "NOT OK"
}
} Else {
$Script:log += "ClassInstance ($ClassInstance) Not Found or already in Maintenance"
SqlDataManagement -QueryType update_not_found
Send-Email -Status "NOT OK"
}
}
$SCOrchLog = $script:log
Runbook Design (Insert) :
Runbook Design (Process) :
Later i'll post on how to tell the third-party could "consume" the Insert into DB runbook as webservice.
:)
