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: http://blogs.technet.com/b/servicemanager/archive/2011/04/06/faq-why-can-t-i-add-some-columns-that-i-want-to-views.aspx.
At a high-level, here is how I solved this and here's the policy I created in Opalis specifically for the SQL team.
- Use Monitor Object to look for newly created or updated IRs
- 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.
- Query the ServiceManager DB to match the GUID of the Affected Item to it's Principal Name
- Repeat steps 2 and 3 for other classes such as SQL DB Engine or SQL Database
- 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.
- Windows Computer
- SQL DB Engine
- 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:
WHERE BaseManagedEntityId = 'Related Object Guid from...'
For SQL 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.