.\Matthew Long

{An unsorted collection of thoughts}

Query a database without scripting as part of SCOM monitoring – The System.OLEDBProbe module

Posted by Matthew on June 23, 2012

A fairly common monitoring scenario is the need to query a database somewhere (normally SQL, but as long as you have a relevant OLEDB driver on your agents, whatever you need!) and based on the results of the query trigger some kind of workflow. I’ve seen it’s used with monitors, Alert and collection rules and even Discoveries!

Obviously you can do this via script, but perhaps you have a simple query and no need to do any posts query processing (often this can be done as part of your query anyway). In these cases, you can also use a built in module called the System.OLEDBProbe to query the DB and do the lifting for you!

What is it

The System. Module is a built in probe module that will use a OLEDB provider/driver on the system to make a database query from the hosting agent. The database, query and other settings are defined via probe configuration and do not need to be hard coded into the MP (though obviously the query usually is). The query can be modified using context parameter replacement prior to execution so you can dynamically insert information into it if need be. It supports integrated and also manually specified credentials, usually via Run As Profiles.

It also has the nifty ability to retrieve the database settings from specified registry keys, which can avoid the need to go out and discover those pieces of information. This makes it quite suitable for attaching onto existing classes from other management packs.

When you should use it

  • You know in advance which columns you need to access.
  • You know how to implement your own module.
  • You have a suitable OLEDB provider on your agent (common windows ones included by default)
  • You don’t need to perform complex processing on each returned row.

Configuration

Required

  • ConnectionString – The connection string you wish to use to connect to the database.  On windows 2003 or later, this is encrypted by the module.  if you are using Integrated security, you do not need to specify credentials as long as you are using a run as profile with this module (but make sure you flag the connection as using integrated security!).
  • Query – The query you wish to run against the database. Supports context parameter replacement, so you can use $Config/$ variables etc in your query.

Optional

  • GetValue – (true/false) Whether the results of the query should be returned or not (set to false if you just want to connect to the DB, and you don’t care about the results of the query).
  • IncludeOriginalItem – (true/false) Determines if the resulting data item(s) will contain the item that originally triggered this module.  Note that the data is returned as CData, so you won’t be able to perform XPath queries directly against it.
  • OneRowPerItem – (true/false) Should all resulting data be returned in a single data item, or 1 data item returned for each row in the query results?  Normally setting this to true is more useful, as you’ll often want a condition detection to process each row individually, and you won’t know the order (or number) of resulting rows.
  • DatabaseNameRegLocation – Registry key where we can find the database name.  Must be under the HKLM hive.
  • DatabaseServerNameRegLocation – Registry key where we can find the database server name (and instance, if required).  Must also be under the HKLM hive.

SCOM 2007 R2 and above only

  • QueryTimeout – (Integer) Optional parameter that allows you to perform a query timeout.
  • GetFetchTime – (true/false) Optional parameter that allows you to specify that the resulting data item(s) should contain the fetch time for the query.

Personally, I tend to omit the R2 only parameters as they usually do not add much to the workflow and will restrict your environment.  Obviously if you are making this MP for inhouse resources you are free to implement against whatever version of SCOM you have!

An important parameter is the OneRowPerItem.  If set to false when you get back data the data item will look like the below snippit (i’ve omitted the other elements to save space)


<RowLength></RowLength>
    <Columns>
    <!-- Data for first row returned -->
       <Column>Data in first column</Column>
       <Column>Data in Second column.</Column>
    </Columns>
    <Columns>
    <!-- Data for Second row returned -->
       <Column>Data in first column</Column>
       <Column>Data in Second column.</Column>
    </Columns>

This can make processing the results in further modules a pain, since your XPath Query is going to have to specify which row and column specifically you want to access. If you instead set OneRowPerItem to true then you’ll get multiple return items and can filter them using an Expression filter with a simple syntax such as $Data/Columns/Column[1]$ You may also wish to filter on the RowLength property to establish if any rows were returned. Remember that the module will return a data item if it succeeded to connect but doesn’t have rights to query, so check that data was returned before you try to do something with it!

Example scenarios

Normally if I’m going to use an OleDBProbe to access a database repeatedly I’ll create my own probe module that sets up the settings I’m going to need and is already set to use my MP’s run as profile for DB access.  That way I don’t have to keep specifying it over and over again.  Below is a sample where I’ve done this, and configured everything other than my query to pass in for a SQL database probe.  Now all my monitors and rules that make use of this know where to locate the DB and what other query options to use automatically (along with credentials).

<ProbeActionModuleType ID="DBProbe.Library.Probe.DatabaseOledbQuery" Accessibility="Public"   RunAs="DbProbe.Library.SecureReference.Database" Batching="false" PassThrough="false">
    <Configuration>
<xsd:element minOccurs="1" name="Query" type="xsd:string" />
<xsd:element minOccurs="1" name="OneRowPerItem" type="xsd:boolean" />
    </Configuration>
<ModuleImplementation Isolation="Any">
        <Composite>
            <MemberModules>
<ProbeAction ID="PassThru" TypeID="System!System.PassThroughProbe" />
<ProbeAction ID="OledbProbe" TypeID="System!System.OleDbProbe">
                    <ConnectionString>Provider=SQLOLEDB;Integrated Security=SSPI </ConnectionString>
$Config/Query$
                    <GetValue>true</GetValue>
                    <IncludeOriginalItem>false</IncludeOriginalItem>
$Config/OneRowPerItem$
                    <DatabaseNameRegLocation>SOFTWARE\MyRegKey\Database\DatabaseName</DatabaseNameRegLocation>
                    <DatabaseServerNameRegLocation>SOFTWARE\MyRegKey\Database\DatabaseServerName</DatabaseServerNameRegLocation>
ProbeAction>
            </MemberModules>
            <Composition>
                <Node ID="OledbProbe">
                    <Node ID="PassThru" />
                </Node>
            </Composition>
        </Composite>
ModuleImplementation>
    <OutputType>System!System.OleDbData</OutputType>
    <TriggerOnly>true</TriggerOnly>
ProbeActionModuleType>

Here I’ve done the same thing, only without using registry keys to specify the location of my DB.  Normally I’d pass the DB details from my targeted class as I’ll have some property that has been discovered defining where the database is.

<ProbeActionModuleType ID="DBProbe.Library.Probe.DatabaseOledbQuery" Accessibility="Public"  RunAs="DbProbe.Library.SecureReference.Database" Batching="false" PassThrough="false">
    <Configuration>
<xsd:element minOccurs="1" name="DatabaseServer" type="xsd:string" />
DatabaseName" type="xsd:string" />
        <xsd:element minOccurs="1" name="Query" type="xsd:string" />
        <xsd:element minOccurs="1" name="OneRowPerItem" type="xsd:boolean" />
    </Configuration>
    <ModuleImplementation Isolation="Any">
        <Composite>
            <MemberModules>
<ProbeAction ID="PassThru" TypeID="System!System.PassThroughProbe" />
            <ProbeAction ID="OledbProbe" TypeID="System!System.OleDbProbe">
Provider=SQLOLEDB;Server=$Config/DatabaseServer$;Database=$Config/DatabaseName$;Integrated Security=SSPI
                <Query>$Config/Query$</Query>
                <GetValue>true</GetValue>
                <IncludeOriginalItem>false</IncludeOriginalItem>
                <OneRowPerItem>$Config/OneRowPerItem$</OneRowPerItem>
            </ProbeAction>
            </MemberModules>
            <Composition>
                <Node ID="OledbProbe">
                    <Node ID="PassThru" />
                </Node>
            </Composition>
        </Composite>
    </ModuleImplementation>
    <OutputType>System!System.OleDbData</OutputType>
    <TriggerOnly>true</TriggerOnly>
</ProbeActionModuleType>

Simple/Specified Authentication

If you don’t (or can’t) want to use Integrated security, you can pass credentials using simple authentication and a run as profile. DO NOT hard code the credentials, these are now stored in plain text and readable. The run as profile creds are encrypted and the connection string is encrypted across the wire, the MP isn’t!

The syntax for this is (depending on your Ole provider, here it’s SQL) shown below.  Obviously replace the text in italics with your values.

Provider=SQLOLEDB;Server=ServerName;Database=databaseName; User Id=$RunAs[Name=”RunAsIdentifierGoesHere“]/UserName$;Password=$RunAs[Name=”RunAsIdentiferGoesHere“]/Password$

Scenario 1 – Monitoring

Fairly simple one this, you want to monitor a database for a certain condition.  Perhaps you are getting the result of a stored procedure, checking the number of rows in a table (by using the databases query langauge) or checking rows for a certain value (perhaps error logs?).  Once queried, you pass the data items onto an System.ExpressionFilter module to filter for your desired criteria and alert as appropriate.

Scenario 2 – Collection

Another fairly common one, do the same thing as above as part of an event collection or performance collection rule.  This could even be ignoring the data and just checking how long it took the query to run, via the InitializationTime, OpenTime, ExecutionTime and FetchTime (if you’re R2 or 2012) properties of the output data.  Following your System.OleDBProbe module you’ll usually use one of the mapper condition detection modules to generate event or performance data (these are quite nicely documented around the web and on MSDN.  Normally done with property bags, but the principle is the same).

Scenario 3 – Discovery

Yep, you can even do discovery from this.  Your table might contain pointers to apps in a grid or distributed system, groups you want to discover and monitor or subprocesses you want to go and do further monitoring on.  This is the most complex scenario and as a tip, only really attempt this if you are looking to discover a single object out of the process per discovery.  Otherwise, use a script to process each result item in turn using ADO or some other API.

Links

MSDN Documentation – http://msdn.microsoft.com/en-us/library/ff472339.aspx

Sample of the output this module returns – http://msdn.microsoft.com/en-us/library/ee533760.aspx

Hopefully that’s given you some food for thought, and as always if you have a specific example you’d like me to walk you through, just post a comment and i’ll see what I can do!

Advertisements

3 Responses to “Query a database without scripting as part of SCOM monitoring – The System.OLEDBProbe module”

  1. […] If you want to query the OpsMgr database without using a script, take a look through Matthew’s post and learn a bit about the SystemOLEDBProbe module:  Query a database without scripting as part of SCOM monitoring – The System.OLEDBProbe module […]

  2. JDMils said

    This is nice for those who know how to set the function up, but for those of us who have dived into SCOM (2012) for the first time….where and how do I set this up?

    • Matthew said

      This blog post discusses how to setup Monitoring of a SQL Database in a custom SCOM management pack. It’s really aimed at MP authors who are quite familiar with creating custom discoveries/monitors/rules already, and may just be unaware of this module.

      If you are looking to setup a synthetic transaction or health query for an existing Db as a SCOM Operator, you’d be better off looking at the OleDB Datasource template that’s available in the SCOM console (Authoring->Management Pack Templates, Ole DB Data Source) as that will do all the plumbing of setting this module up for you. It’s pretty well documented on the Web as well!

      If this is however your first foray into MP authoring, you’d probably need to do some background reading on creating your own SCOM workflow (I assume this is for a monitor, so likely a composite MonitorType) before this will be of much use. If you have a particular use in mind, if you can elaborate I can probably give you better advice and point you in the right direction, or put something together as a sample blogpost 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s