How do I collect data from a multi-instance object – like a SQL DB instance?

 

This was a common question in the RTM version of the product. 

 

If I wrote a rule to collect performance data against a SQL DB engine – I needed to specify the “instance” name.

 

The most common approach, using SQL as an example – was to target SQL DB engine, then on the “Performance Object, Counter, and Instance” page – click “browse” and choose a specific instance to use as an example.

Here lies the rub:  If you browsed and chose a server hosting a single default instance as the example – it will populate the Object with something like:  SQLServer:Databases”.   Good?  No.  This will ONLY bring back data from any of the default instances of SQL we discover.  Any named instances will be ignored.

Ok…. So let’s pick a named instance then?  If we browse, and use a named instance – we get an object that looks like so:  MSSQL$Instance1:Databases”.  Good?  Nope, try again.  This rule will ONLY collect for that specific named instance…. and no other.

 

So…. How do we write a rule to collect data from ANY instance of SQL?

 

This is documented with good solid examples on Boris’s blog:

http://blogs.msdn.com/boris_yanushpolsky/archive/2007/11/21/opsmgr-sp1-creating-rules-and-monitors-for-multi-instance-components.aspx

 

Essentially – Microsoft has added a flyout button to the right of “Object” that will let us choose the correct object for OpsMgr to collect based on our desired criteria:

 

From Boris’s blog post:

 

In order to specify the proper performance object name for your rule/monitor that will work properly both for default instances and named instances as well as when multiple instances are installed you will need to do the following:

1.       For the performance object name you will need to use the Performance Counter Object Name property. This will result in an expression which will be replaced at runtime with the right instance name.

2.       Because the performance object name also contains additional text in addition to the instance name you will need to add this text manually in the performance object name edit box. The performance object name should look like this: $Target/Property[Type="SQLServer!Microsoft.SQLServer.DBEngine"]/PerformanceCounterObject$:Databases