Monday, November 28, 2011

Add Affected Items to the Title or Description of a Service Manager IR

I recently began creating Incidents in Service Manager from alerts created in Operations Manager via the SCOM Alert Connector.  I'm pushing this out slowly to the various groups (SQL, Windows Server, App Support, etc...) so I can pay more attention to each one as I roll it out and do some hand holding.  The SQL group was welcoming of it last week, but requested that the Affected Item(s) show in the Title of the Incident so they can quickly see (either via the e-mail notification or in the console) which server or DB was affected because otherwise you have to open the ticket and scroll down and you might end up with a situation like this:

I know a DB is offline, some IS Packages failed, etc... but considering I have thousands of DBs and many packages... this view doesn't really help me if I want to quickly identify which DB is offline.

I reached out to Travis and asked him how to do this.  He responded with this blog post which says it's not possible natively within Service Manager because the max cardinality for those relationships can be > 1.  More info here:

At a high-level, here is how I solved this and here's the policy I created in Opalis specifically for the SQL team.  
  1. Use Monitor Object to look for newly created or updated IRs
  2. Get the relationship between the object GUID returned from Monitor Object and whatever class you're looking for.  In this case, I chose Windows Computer class.
  3. Query the ServiceManager DB to match the GUID of the Affected Item to it's Principal Name
  4. Repeat steps 2 and 3 for other classes such as SQL DB Engine or SQL Database
  5. Update Incident
Since depending on what alert is thrown, it's going to use items from different classes, you might need to add many get-relationship and query sql objects.  In my example, I've added three since for the alerts we receive, they seem to all involve three classes.
  1. Windows Computer
  2. SQL DB Engine
  3. SQL Database
Here are the screenshots from the first 3 objects and then the last one:

To query the SQL DB Engine, you'll need this:
USE ServiceManager
SELECT [AgentName_69B79706_8AE1_470D_5FD7_0AAA7A126CCA]
FROM [ServiceManager].[dbo].[MT_Microsoft$SQLServer$DBEngine]
WHERE BaseManagedEntityId = 'Related Object Guid from...'

For SQL Database:
USE ServiceManager
SELECT [DisplayName]
FROM [ServiceManager].[dbo].[MT_Microsoft$SQLServer$Database]
WHERE BaseManagedEntityId ='Related Object Guid from...'

You'll just have to use SQL Management Studio and dig inside the Service Manager DB to find which table and column you need to query depending on the object.

Now when a new IR comes through, the policy will run and the Title will look like this:

If you rather, you can update the Description field or both.  Just add or modify the Fields section in the Update Object Properties.

No comments:

Post a Comment