SCCM (ConfigMgr) ADR Maintenance Mode in SCOM (Powershell and SCOrch)

One thing we all miss in SCCM, is the fact of the option "Disable Operations Manager alerts while software updates run" doesn't really disable all the alarmistic for a OpsMgr agent, specially if reboot is needed, and of course it'll cause alarms on the agent being updated.

After some googling i didn't find any solution to put ADR Collection Members into Maintenance in OpsMgr, i started to code some powershell.

If you might remember my later post about "OpsMgr (SCOM) - Schedule Maintenance Mode" i used the same idea to put collection members into maintenance mode.

So before the powershell script that gives me all the ADR Collection Members i had to made some changes into my OpsMgr_MM database.
I've added a new collumn "ADR_ID".

To add it just run the following SQL query :

 ALTER TABLE Scheduling  
 ADD ADR_ID varchar(100);  

Also changed the runbook powershell script that puts the agents into maintenance to this :

 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 { "" }  
 $script:SQLServer = "" # Your SQL Server  
 $script:SQLDBName = "OpsMgr_MM"  # Your Database
 $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 = ""  
 $script:ClassInstance = ""  
 $script:table = ""  
 # Connection to OpsMgr Management Group  
 try{  
   $MGConnSetting = New-Object Microsoft.EnterpriseManagement.ManagementGroupConnectionSettings('Your_SCOM_SERVER')  
   $MG = New-Object Microsoft.EnterpriseManagement.ManagementGroup($MGConnSetting)  
 } Catch { ' ' }  
 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  

The changes are :
- Function "SqlDataManagement" now accepts 'update_not_found' parameter for not found agents in OpsMgr
- Function "Get-SCOMObjectbyClass" now makes the criteria LIKE instead of = (SCCM agents are listed as HOSTNAME instead of the FQDN)
- If the agent is not found it also let you know sending you an e-mail.

So, since we've got it all done before running our new PS1 script, the script it self :

 Import-Module "D:\Program Files\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1"  
 cd SITE_CODE:  
 $logFile = 'Your Path to SCCM_MM.log'  
 $MaintenanceWindowMode = 'Collection'  
 $SQLServer = "" #Your Server    
 $SQLDBName = "OpsMgr_MM" #YourDBName   
 $LimitDate = Get-Date  
 $AutoDeployRules = @()  
 Foreach ( $ADR in (Get-CMAutoDeploymentRule -Fast )) {   
   $ADRSchedule = (Convert-CMSchedule ($ADR.Schedule) | Select StartTime).StartTime  
   If ( $ADRSchedule -ge $LimitDate -and $ADR.LastRunTime -le $LimitDate) {  
     $AutoDeployRules += $ADR  
   }  
   Else { $ADR.Name + ' not reliable to Maintenance - Maintenance Window is in the past!' >> $logFile }  
 }  
 Foreach ( $ValidADR in $AutoDeployRules ) {  
   #ADR Info ----> SELECT NAME, Schedule  
   $ADRID = ($ValidADR.UniqueIdentifier).Guid  
   $ADRMaintenanceStart = (Convert-CMSchedule $ValidADR.Schedule).StartTime  
   $ADRName = $ValidADR.Name  
   # Check if ADR is already in Maintenance Mode Database #  
   $connString = "Data Source=$SQLServer;Initial Catalog=$SQLDBName;Integrated Security = True"   
   $connection = New-Object System.Data.SqlClient.SqlConnection($connString)    
   $connection.Open()   
   $sqlcmd = $connection.CreateCommand()    
   $SqlQuery = "set dateformat dmy ; SELECT * FROM Scheduling WHERE ADR_ID = '$ADRID' AND [StartTime] != '$(Get-Date $ADRMaintenanceStart -Format g)' ;"   
   $sqlcmd.CommandText = $SqlQuery    
   $result = $sqlcmd.ExecuteReader()  
   If ( $result.HasRows -eq $False ) {   
     If ( $MaintenanceWindowMode = "ADR" ) {  
       # <Duration>1</Duration><DurationUnits>Hours</DurationUnits>  
       [xml]$ADRDeploymentTemplate = $ValidADR.DeploymentTemplate  
       [Int32]$ADRDuration = $ADRDeploymentTemplate.DeploymentCreationActionXML.Duration  
       $ADRDurationUnits = $ADRDeploymentTemplate.DeploymentCreationActionXML.DurationUnits  
       # ADR Stop Maintenance Calculation  
       Switch ($ADRDurationUnits){  
         Hours { $ADRMaintenanceStop = (Get-Date $ADRMaintenanceStart).AddHours($ADRDuration) ; break }  
         Days { $ADRMaintenanceStop = (Get-Date $ADRMaintenanceStart).AddDays($ADRDuration) ; break}  
         Weeks { $ADRMaintenanceStop = (Get-Date $ADRMaintenanceStart).AddDays( $ADRDuration * 7 ) ; break}  
         Months { $ADRMaintenanceStop = (Get-Date $ADRMaintenanceStart).AddMonths($ADRDuration) ; break}  
        }  
     }  
     If ( $MaintenanceWindowMode = "Collection" ) {  
      $ADRMaintenanceStop = (Get-Date $ADRMaintenanceStart).AddMinutes(((Get-CMCollectionSetting -CollectionId $ValidADR.CollectionID | select -ExpandProperty ServiceWindows | select Duration).Duration))  
     }  
     # ADR Collection Members Info  
     $ADRCollectionName = (Get-CMCollection -Id $ValidADR.CollectionID).Name  
     $ADRCollectionMembers = Get-CMCollectionMember -CollectionId $ValidADR.CollectionID  
     Foreach ( $CMDeviceMember in $ADRCollectionMembers ) {  
       $CMDevice = $CMDeviceMember.Name   
       $connString = "Data Source=$SQLServer;Initial Catalog=$SQLDBName;Integrated Security = True"   
       $connection = New-Object System.Data.SqlClient.SqlConnection($connString)    
       $connection.Open()   
       $sqlcmd = $connection.CreateCommand()    
       $SqlQuery = "set dateformat dmy ; INSERT INTO Scheduling (ci,type,Team,StartTime,EndTime,Comment,Status,ADR_ID) VALUES ( '$CMDevice', 'Computer', 'SCCM', '$(Get-Date $ADRMaintenanceStart -Format g)', '$(Get-Date $ADRMaintenanceStop -Format g)', 'SCCM MaintenanceMode for : $ADRCollectionName | $ADRName' , 'NOT PROCESSED', '$ADRID');"   
       $sqlcmd.CommandText = $SqlQuery    
       $result = $sqlcmd.ExecuteNonQuery()  
     }  
   } Else { $ADRName + ' already in MM Database' >> $logFile }  
 }  

Now, you just need to create a scheduled task in your SCCM server to run whenever you might like, and ... :

# The Scheduled Task :



# OpsMgr Console Maintenance Mode window :



And that's it!

If you bump into some error or bug, please let me know, this is just too fresh and made just some few tests.

Cheers,

Detecting Windows License Activation Status Using ConfigMgr DCM and OpsMgr

If someone already done the work, why not share it ?

Tao Yang's post about this is amazing, like all the other posts he makes.

http://goo.gl/84qkX1

I followed his post until the end, and suddenly i came up with some errors in SCCM about the Powershell Scripts Execution Policie ...!

So, i've made this VBScript for the workaround (already post it too in Tao's blog!)

Just replace the part of the Powershell script with this VBScript if you bump into some execution policie issue.

 strComputer = "."   
 Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")   
 Set colItems = objWMIService.ExecQuery( _  
   "Select * from SoftwareLicensingProduct Where PartialProductKey IS NOT NULL AND ApplicationID = '55c92734-d682-4d71-983e-d6ec3f16059f'",,48)   
 For Each objItem in colItems   
 select case objItem.LicenseStatus  
         case "0"  
                 Wscript.Echo "Unlicensed"  
         case "1"  
                 Wscript.Echo "Licensed"  
         case "2"  
                 Wscript.Echo "Out-of-Box Grace Period"  
         case "3"  
                 Wscript.Echo "Out-of-Tolerance Grace Period"  
         case "4"  
                 Wscript.Echo "Non-Genuine Grace Period"  
         case "5"  
                 Wscript.Echo "Notification"  
         case "6"  
                 Wscript.Echo "ExtendedGrace"  
 end select  
 Next  

Cheers,

Powershell - SCOM (OpsMgr) Distributed Application to SCCM (ConfigMgr) Collection

For reporting purposes i had to create equal SCCM collections with the same members that i had in SCOM Distributed Applications.
Now that i have same DA as Collections, and respective members, i can match alerts (DA from SCOM), as well i can have a list of required updates (Collection from SCCM) in the same PowerBI report.
It can be really useful for Application Owners or Sys Admin teams.

Instead of creating by hand every DA I've in SCCM as a collection, and since i've got around 60 DA's, i came up with this script!
(Sorry for the variable names, and for some bad code - not having the time i need to get it better!)
(PS: Read the comments before you run the script! :) )

 Import-Module OperationsManager  
 # Your SCCM Server  
 $SCCMServer = 'Your SCCM Server'  
 $Class = Get-SCOMClass -DisplayName 'User Created Distributed Application'  
 $DistrApps = Get-SCOMClassInstance -Class $Class  
 $DARelationList = ''  
 $ListaDAandHosts = @()  
 Foreach ($DA in $DistrApps) {  
   $DAHosts = ($DA | % {$_.GetRelatedMonitoringObjects()} | % {$_.GetRelatedMonitoringObjects()}).DisplayName  
   Foreach ($Hostz in $DAHosts) {  
     $ListaDAandHosts += $DA.DisplayName + ';' + ($Hostz -split '\.')[0]  
   }  
 }  
 #This is because i only want DA with valid hostnames!  
 $RegexQuery=[regex]"^[0-9A-Za-z].*;(?=.{1,255}$)[0-9A-Za-z](?:(?:[0-9A-Za-z]|-){0,61}[0-9A-Za-z])?(?:\.[0-9A-Za-z](?:(?:[0-9A-Za-z]|-){0,61}[0-9A-Za-z])?)*\.?$"  
 $ListaDA = @()  
 $ListaDA += 'DA;ServerFQDN'  
 Foreach ($line in $ListaDAandHosts) {  
   If ($RegexQuery.Match($line).Success -eq $true) {      
       $ListaDA += $RegexQuery.Match($line).Groups[0].Value  
   }  
 }  
 # Set the out-file as you like!  
 $ListaDA | Out-File "\\\$SCCMServer\c$\FOLDER\ListaDA.csv"  
 $SCCMScriptBlock = {  
   Import-Module "D:\Program Files\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1"  
   $DACSV = Import-csv 'C:\GDC\ListaDA.csv' -Delimiter ';'  
   $DAList = ($DACSV | Group {$_.DA.Substring(0)}).Name  
   # Set Location to Site Name #  
   Set-Location YOUR_SITE_NAME:  
   # Your SCCM Limit Collection #  
   $Limitingcollections = "All Systems"  
   # Let the show begin #  
   Foreach ($DA in $DAList) {  
     $DAHostList = @()  
     $DAHosts = (($DACSV | ? { $_.DA -eq $DA } | Select ServerFQDN).ServerFQDN)  
     # Create DA If not Exists #    
     If (Get-CMDeviceCollection -Name $DA) {  
       # Does Nothing #  
       $DA + ' | Already exists!'  
     } Else {  
       $DANewCollection = New-CMDeviceCollection -Name "$DA" -LimitingCollectionName $Limitingcollections  
     }  
     # Add each host to Collection #  
     Foreach ( $SCCMAgent in $DAHosts ){  
       Try {  
         Add-CMDeviceCollectionDirectMembershipRule -CollectionName "$DA" -ResourceID $(get-cmdevice -name "$SCCMAgent").ResourceID  
       } Catch {  
         $SCCMAgent + ' | Already in Collection or not found'  
       }  
     }  
       # Move your collection to specific location - if you want to #  
     Move-CMObject -FolderPath 'SITE_NAME:\DeviceCollection\YOUR_SPECIFIC_FOLDER' -InputObject $DANewCollection  
   }  
 }  
 $SCCMSession = New-PSSession -ComputerName $SCCMServer  
 Invoke-Command -Session $SCCMSession -scriptblock $SCCMScriptBlock  

And, that's it!