Welcome to Part 5 on how to do crazy stuff for Operations Manager using SQL, XML and PowerShell (and now Excel!). In the next part of this blog series we will investigate how to use PowerShell and Excel together to provide an important piece of information when integrating Operations Manager into a third party ticketing system.

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 Part 3 we covered how to use PowerShell and SQL to work with multiple management group configurations.

In Part 4 we covered what information is stored in the database when creating overrides and how Advisor can store configuration information for Operations Manager in the cloud.


Finding the name of the related computer object for almost any object in Operations Manager

When working with a third party ticketing system it is helpful to be able identify what computer object the various objects in Operations Manager are associated. The alert is used by the ticketing system so if the alert does not include the relevant computer object we can look up the computer object in a mapping table which maps the object to the relevant computer name.

To address this, we can create a mapping table for objects which are alerted from and what computer they are associated with. This mapping table can be used so that tickets can be effectively routed to a group based upon the computer object. Please note that the information available in this process is based upon what alerts are currently available in the Operations Manager environment. If there are no alerts from a specific GUID we will not have a mapping for that object to the relevant computer object at the end of this process. By increasing the alert retention in Operations Manager (administration pane, settings) and running after more data has been collected we can gather more mapping information since more alert information will be available.


Building the mapping table content from PowerShell

The NetbiosComputerName field on get-scomalert is populated for most of the alerts but not for all of them depending on what the object is that the alert is based on. The bulk of a mapping table would only have the MonitoringObjectId and the NetbiosComputerName field. An example PowerShell to get this is listed below:

Get-scomalert | where {$_.NetbiosComputerName –ne $null} | select MonitoringObjectId,NetbiosComputerName > c:\temp\uniqueids1.txt

An example output for this script is below which shows the MonitoringObjectId and NetbiosComputerName field that we could map to the computer name (once we remove duplicate entries in Excel).

The PowerShell script below shows alerts which exist and do not have a value for the NetbiosComputerName field. On get-scomalert there are two other fields which may have the computer name information:

  • MonitoringObjectDisplayName
  • MonitoringObjectName

Get-scomalert | where {$_.NetbiosComputerName –eq $null} | select MonitoringObjectId,MonitoringObjectDisplayName,MonitoringObjectName > c:\temp\uniqueids2.txt

An example output for this script is below which shows the MonitoringObjectId and the two potential fields that we could map to the computer name (again assuming it’s not already in the NetbiosComputerName field).

In the example above, the MonitoringObjectName has what appears to be the computer name. In the next several lines the computer name is available within the MonitoringObjectDisplayName field.

There are a few minor exceptions that I have seen from this script:

  • Operations Manager: Alerts from Operations Manager itself appear to not have a value which looks like a computer name in either of these two additional fields. For our mapping table purposes I map those MonitoringObjectId’s to the management server functioning as the RMSe.
  • Cluster alerts: Alerts from clusters have a somewhat strange naming but they are able to be extracted using the methods documented here in Excel.



Using Excel to manipulate the PowerShell results

There may have been ways to do this in PowerShell as well (if you are aware of them please post your comments here!). For my testing once we create these files, I used Excel to remove any duplicate entries. To do so copy over the uniqueids(1 and 2) files that we created and open the in Excel as a fixed width configuration like the one shown below:

The results in Excel look like this.

All we want is a single record which maps the unique objects available. We can remove duplicates from Excel by using the on the data tab and removing duplicates for the MonitoringObjectId.

This gives us a result of the various MonitoringObjectId’s and their NetbiosComputerName results. A subset of these are shown below:

Using this same process we can open the second output file in Excel as a fixed length data file. Use the following excel formulas to identify what the correct (IE: Netbios type name) string is:


The formula searches for data which match a GUID format (some digits and a – several times, see the screenshot below in column C5 for an example). If it has a GUID type format it’s assumed that the other field has more valid data. The D2 field as an example looks like this: [Be careful that the quotes are correct. " is the incorrect one. " is the correct one]


The E2 field as an example looks like this: [Be careful that the quotes are correct. " is the incorrect one. " is the correct one]

The results are not 100% automated as you will need to remove the FQDN pieces from the various systems which identify FQDN’s (see cell E6 above), and strings which match a GUID format (like the All-In-One-Cloud7-Isolated example) will need to be manually resolved. For the example above, the manual resolution would be to copy the contents of cell B5 to cell E5. After hiding cells B, C and D we get the two fields that we want to have for the output.

Once duplicates are removed, we have the next part of our mapping table as shown below.

Finally, we remove any FQDN’s and incorrect resolutions (like the Default-First-Site-Name) from the table. The final result for my lab environment was a single table as shown below which matches the various MonitoringObjectId’s to their appropriate NetBios name (for my environment there about 90 unique records remaining).

























































Summary: Using PowerShell and Excel we can gather information which can be used for practical purchases such as providing a mapping table for third party ticketing systems so that alerts can be mapped to their relevant NetBios computer names.