Welcome to Part 3 on how to do crazy stuff for Operations Manager using SQL, XML and PowerShell.

In Part 1 we covered places where Operations Manager could store data and used unique search stings to see what that data is stored either in the XML, registry, or in the databases.
In Part 2 we covered how to find rules and monitors which match specific conditions (like running scripts or items which run at a specific interval).

In this blog post we will look into using PowerShell and SQL to help with working with multiple management groups Operations Manager:

  • Comparing management packs in different management groups (one way in PowerShell and another in SQL)
  • Comparing Operations Manager agents in different management groups (one way in PowerShell and another in SQL)

In a multiple management group configuration it’s often helpful to be able to easily identify what management packs are not the same between the environments and what agents are the not the same between the environments.

Comparing management packs in different management groups – PowerShell

In a multi-homed configuration it’s often useful to know if an agent is reporting to both management groups and to know the differences in management packs between the management groups. This is useful in a multi-homed migration as well as in situations where agents are multi-homed to different management groups to provide datacenter level redundancy. So what’s a quick way to find these things out? Remember, PowerShell and SQL queries is our friend!

This script is built from a version which Matt Dowst wrote to compare management pack differences in Service Manager which is available at: https://www.catapultsystems.com/mdowst/archive/2013/07/08/compare-production-amp-development-management-packs-powershell.aspx

# #############################################################################

# File: MP_Comparison.ps1

#

# Author: Matthew Dowst, Catapult Systems

# Date: 07/05/2013

#

# Purpose: This script will compare management packs between two Operations Manager

# Management Groups.

#

#

# Version History

# 1.0 – 07/05/2013 – Initial Version

# 1.1 – 06/03/2014 – Updated to work on Operations Manager by Cameron Fuller

#

# #############################################################################

 

#Enter Development Server Information

$DevSRV = ‘Server Netbios or FQDN’

$DevUser = "domain\user"

$DevPwd = "password"

 

#Enter Production Server Information

$ProdSRV = ‘Server Netbios or FQDN’

$ProdUser = "domain\user"

$ProdPwd = "password"

 

###################################################

# #

# DO NOT MAKE ANY CHANGES BELOW THIS LINE #

# #

###################################################

#Set credential variables

$DevPwd = $DevPwd | ConvertTo-SecureString -asPlainText -Force

$DevCred = New-Object System.Management.Automation.PSCredential($DevUser,$DevPwd)

$ProdPwd = $ProdPwd | ConvertTo-SecureString -asPlainText -Force

$ProdCred = New-Object System.Management.Automation.PSCredential($ProdUser,$ProdPwd)

 

#Set outputs to blank

$NotInDev = ""

$VerMisMatch = ""

$NotInProd = ""

 

 

#Get Production Management Packs

$Prod = Get-SCManagementPack -ComputerName $ProdSRV -Credential $ProdCred | sort-object DisplayName

Foreach ($Prod in $Prod)

{

$ProdName = $Prod.name

#Compare Development Management Pack Names and Version Numbers

    $Dev = Get-SCManagementPack -name $ProdName -ComputerName $DevSRV -Credential $DevCred

If ($Prod.name -ne $Dev.name)

{

$NotInDev = $NotInDev + $Prod.DisplayName + "n"

}

Else

{

If ($Prod.Version -ne $Dev.Version)

{

$VerMisMatch = $VerMisMatch + $Prod.DisplayName + " - " + $Prod.Version + " <> " + $Dev.Version + "n"

}

}

}

 

#Get Development Management Packs

$Dev = Get-SCManagementPack -ComputerName $DevSRV -Credential $DevCred | sort-object DisplayName

$NotInProd = ""

Foreach ($Dev in $Dev)

{

$DevName = $Dev.name

#Compare Production Management Pack Names

    $Prod = Get-SCManagementPack -name $DevName -ComputerName $ProdSRV -Credential $ProdCred

If ($Prod.name -ne $Dev.name)

{

$NotInProd = $NotInProd + $Dev.DisplayName + "n"

}

}

 

Write-Host "Not in Development"

Write-Host "------------------"

$NotInDev | sort-object DisplayName

 

Write-Host "Not in Production"

Write-Host "------------------"

$NotInProd | sort-object DisplayName

 

Write-Host "Different Version in Development"

Write-Host "------------------------"

$VerMisMatch

#End of Script

 

Comparing management packs in different management groups - SQL

Robert provided a simple query which will compare the management packs loaded in two different environments which is available at: http://scnuggets.blogspot.co.uk/2013/11/management-pack-difference-report.html. The slightly updated query is available below:

select MPFriendlyName, MPName, MPVersion

from <TestDBServer>.OperationsManager.dbo.ManagementPack as Test

where not exists

(select *

from <ProdDBServer>.OperationsManager.dbo.ManagementPack as Production

where Test.MPNAME like Production.MPNAME

and Test.MPVERSION = Production.MPversion)

ORDER BY MPFriendlyNamePlease note that to be able to use this query you must run the following for each server which you are not locally logged onto and running the SQL query from:

execute sp_addlinkedserver <DBServerName>

 

Comparing Operations Manager agents in different management groups - PowerShell

This script provides a comparison of the agents which are installed in two different management groups. This script is built from a version which Matt Dowst wrote to compare management pack differences in Service Manager which is available at: https://www.catapultsystems.com/mdowst/archive/2013/07/08/compare-production-amp-development-management-packs-powershell.aspx

# #############################################################################

# File: Agent_Comparison.ps1

#

# Author: Matthew Dowst, Catapult Systems

# Date: 07/05/2013

#

# Purpose: This script will compare agents between two different Operations Manager

# management groups.

#

# Version History

# 1.0 - 07/05/2013 - Initial Version

# 1.1 - 06/03/2014 - Updated to work on Operations Manager by Cameron Fuller

# 1.2 - 06/04/2014 - Changed to report on differences of agents by Cameron Fuller

#

# #############################################################################

 

#Enter Development Server Information

$DevSRV = 'Server Netbios or FQDN'

$DevUser = "domain\user"

$DevPwd = "password"

 

#Enter Production Server Information

$ProdSRV = 'Server Netbios or FQDN'

$ProdUser = "domain\user"

$ProdPwd = "password"

 

###################################################

# #

# DO NOT MAKE ANY CHANGES BELOW THIS LINE #

# #

###################################################

#Set credential variables

$DevPwd = $DevPwd | ConvertTo-SecureString -asPlainText -Force

$DevCred = New-Object System.Management.Automation.PSCredential($DevUser,$DevPwd)

$ProdPwd = $ProdPwd | ConvertTo-SecureString -asPlainText -Force

$ProdCred = New-Object System.Management.Automation.PSCredential($ProdUser,$ProdPwd)

 

#Set outputs to blank

$NotInDev = ""

$VerMisMatch = ""

$NotInProd = ""

 

 

#Get Production Agents

$Prod = Get-SCOMAgent -ComputerName $ProdSRV -Credential $ProdCred | sort-object DisplayName

Foreach ($Prod in $Prod)

{

$ProdName = $Prod.name

#Compare Agent Names and Version Numbers

    $Dev = Get-SCOMAgent -name $ProdName -ComputerName $DevSRV -Credential $DevCred

If ($Prod.name -ne $Dev.name)

{

$NotInDev = $NotInDev + $Prod.DisplayName + "n"

}

Else

{

If ($Prod.Version -ne $Dev.Version)

{

$VerMisMatch = $VerMisMatch + $Prod.DisplayName + " – " + $Prod.Version + " <> " + $Dev.Version + "n"

}

}

}

 

#Get Development Agents

$Dev = Get-SCOMAgent-ComputerName $DevSRV -Credential $DevCred | sort-object DisplayName

$NotInProd = ""

Foreach ($Dev in $Dev)

{

$DevName = $Dev.name

#Compare Production Agent Names

    $Prod = Get-SCOMAgent -name $DevName -ComputerName $ProdSRV -Credential $ProdCred

If ($Prod.name -ne $Dev.name)

{

$NotInProd = $NotInProd + $Dev.DisplayName + "n"

}

}

 

Write-Host "Not in Development"

Write-Host "——————"

$NotInDev | sort-object DisplayName

 

Write-Host "Not in Production"

Write-Host "——————"

$NotInProd | sort-object DisplayName

 

Write-Host "Different Version in Dev"

Write-Host "————————"

$VerMisMatch

#End of Script

Comparing Operations Manager agents in different management groups – SQL

I have a client where we are doing a multi-homed migration and agents report to both a 2007 R2 Operations Manager environment and to one of two different Operations Manager 2012 SP1 environments. The original 2007 R2 environment includes both production and test systems. In the 2012 environment the production systems are in one management group and the test systems are in another management group. Since the PowerShell scripts are different between 2007 and 2012 versions of Operations Manager, a SQL script makes it easier to accomplish the task of determining – what agents are monitored by the original environment, but are not being monitored by either of the new environments.

The following is the sample SQL query for 2007 agents which are not reporting to either of two 2012 management groups: (based upon Robert’s script available at http://scnuggets.blogspot.co.uk/2013/11/management-pack-difference-report.html)

 

select PrincipalName

from <2007DBServer>.OperationsManager.dbo.MTV_HealthService as Prod07

where not exists

(select DisplayName

from <2012ProdDBServer>.OperationsManager.dbo.MT_HealthService as Prod12

where Prod07.PrincipalName like Prod12.DisplayName)

AND NOT EXISTS

(select DisplayName

from <2012DevDBServer>.MHUSDOM01.dbo.MT_HealthService as Dev12

where Prod07.PrincipalName like Dev12.DisplayName)

ORDER BY PrincipalName

 

Note: As mentioned earlier, to be able to use this query you must run the following for each server which you are not locally logged onto and running the SQL query from:

execute sp_addlinkedserver <DBServerName>

 

Summary: SQL queries and PowerShell are both able to provide insights which are important to be aware of when working in a multiple management group configuration. These SQL and PowerShell scripts are available for download on System Center Central. In the next part of this series we will review what information is stored when an override is generated and will provide insights on an interesting fourth area where Operations Manager is now storing information.