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:

=IF(ISNUMBER(SEARCH("*-*-*-*-*",B2)),C2,B2)

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]

=IF(ISNUMBER(SEARCH("*-*-*-*-*",D2)),"Unknown",D2)

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).

aae7c641-cb0f-927d-8af3-994c4cdb5e91

All-In-One-Cloud7

8988db24-e267-c245-e2b6-4c5e3a969545

Cloud-db06

9f36b7df-65de-9d36-54ec-13423439a7a2

CLOUD-DC01

2564c630-c757-ffa1-7da0-e2b5ad7c1cc6

CLOUD-DC1

b24cafde-204b-f25c-5064-12aa509acd35

CLOUD-DC1

12245032-ba9a-e38b-9058-7dd639304cfd

CLOUD-DC1

f4ff47e9-9898-c1a9-2bab-1292f6730798

Cloud-DC101

7031f77a-5035-9e75-bb1d-d4e0b5d80cb6

Cloud-DC101

e614c640-0dfc-f89b-b460-a1e438f88b00

Cloud-DC101

f6fb2ec7-c749-c3ca-8882-9ef521a083a1

Cloud-DC101

0856ff42-246e-5a06-bfdc-41f4fdde7c4b

Cloud-DC101

40c2c756-4bdb-1fb6-ccda-0017825d044a

Cloud-DC101

a191e794-6c5f-7e97-9598-dde3028ad43c

Cloud-DC101

a515c61c-3f6d-faa4-cad7-30afde97d979

Cloud-DC101

d418b87b-6a37-efca-19f6-915255152cb1

Cloud-DC102

39f1cd92-e20e-b1d5-8705-2c418636b211

CLOUD-VM03

1d996e77-cd57-4e80-4227-d2814da85629

CLOUD-VM03

f93cd4cb-ec0d-b207-b3c0-097a5de222ac

DB01

e608df1c-1ec0-5d1f-4ceb-aa983af8d197

DB01

97f092c6-1234-8ee3-0faf-4581b2eb9780

DB01

28476c56-63e6-9c79-9f52-ab2a562b746b

DB02

7655d530-440c-91a3-2cf3-aec0c7388a4b

DB02

ac8e5708-bb16-7351-df1e-b1a7c29b5acc

DB02

6c23fd66-5b18-b505-5385-5dffeb26829f

DB02

c8122634-edd2-6542-d964-10dcddf5fdfb

DB02

9593bdbf-ebb7-8bc5-c4cb-b16e5033f778

DB02

c88f249f-9efd-5f2a-f4bf-3f050cfc73f3

DB02

   

 

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.