Reading a database table like a log file

Some applications log status updates to a database table, rather than drop them in a text file or eventlog.  I was able to get OpsMgr to read a database table like a log file, and thought I should post the how-to here, since I’ve seen a few other requests for how to do it.

At a high level, the methodology is to create a new data source, optionally map that data to something resembling Event Data, and then create rules against the data source.  I may be using the built-in OleDB module in an unintended way (I think it was just meant to probe for application database availability), but it has proven very successful so far.

Demo Setup

Let’s start with the database table (I’m making this up for example purposes)

I wrote a quick script to write data to the table as follows.  Pretty basic.  I thought about randomizing the data, but didn’t…

You’ll have to modify the values to get rules and monitors to fire in the MP

declare @i int
select @i =1
while @i<10
begin
    insert MessageLog ([EventCode], Severity, [Message], [TimeStamp], [User],[Computer])
    values             (1, 2, ‘Test Message’, getdate(), ‘Mike’,’Demo’)
    select @i=@i + 1
    waitfor delay ’00:00:03′
end

Other log sources have some way to persist a last-read-entry mark.  There’s no easy way to persist this within OpsMgr.  You could write script or your own external data source using managed code, but that’s not what I was trying to accomplish.  I didn’t want to have to deploy any additional bits on my boxes.  So I used a persistence table.

Initialization

You either have to initialize these manually (e.g. put a "1" in the MessageLogLastRead column) or create it on the fly in the datasource itself.  On the fly would mean detecting whether or not the DBMPPersistence table exists, and if not create it and initialize it with a valid value such as the current max(id) of the MessageLog table.  The behavior would then be that your MP would start reading rows that got logged after the MP was loaded.  You probably don’t want to load an MP and start reading from the beginning of a Message Log table…

Now comes the good stuff. 

Building the MP

RunAs Profile

First, create a Run As Account that can access the database table from OpsMgr.  This is kind of a pain, since creating one in the OpsMgr UI creates it in the Default MP, and the Auth Console doesn’t appear to expose a way to create one.  It goes between </EntityTypes> and <ModuleTypes> in the MP xml.  The format is:

</EntityTypes>
<SecureReferences>
   <SecureReference ID="DBMP.DbAccessAccount" Accessibility="Public" Context="System!System.Entity" />
</SecureReferences>
<ModuleTypes>

You’ll also want a friendly name for this, so enter something useful in the LanguagePack section.  For example:

   <DisplayString ElementID="MyMP.DbAccessAccount">
     <Name>Database Message Log Access Account</Name>
     <Description>Used by MyMP to read from the MessageLog table of the MyAppDB database.</Description>
   </DisplayString>

Save the MP and load it into the authoring console.  I’ve uploaded a completely blank MP, except for having this RunAs account, here.

Remember that once you import the MP, you need to create a RunAs Account, assign it to the DbAccessAccount profile you just created, and associate the profile to the HealthService where the Query will execute.

The DataSource – The root of it all

Now, create a new data source that polls the database table.  The datasource should consist of a System.Scheduler and a System.OleDBProbe module, and should use the RunAs account just created.  I recommend making the Scheduler a configurable parameter, but it can be hardcoded in the datasource.  Similarly, the ConnectionString and Query parameters can either be hardcoded or passed as parameters.  Both have advantages, but it’s important to not make either one overrideable.

There’s no editor page for the System.OleDbProbe module, so you’re getting into XML regardless.  Below is the XML configuration for the System.OleDbProbe module.  More on this later.

Note that the query renames the fields as "1", "2", "3" and so on.  Unlike the the recordset returned from an ADODB.Connection object (e.g. what you might use in VBScript) there is no way to refer to the columns by name.  The recordset is transformed into OpsMgr’s XML OLEDB.DataItem which only allows for referring to column by ordinal number.  The renaming operation (select [eventcode] as [1]) allows you to easily see how to refer to that column in downstream modules (e.g. $Data/Columns/Column[1]$ or $Data/EventData/DataItem/Columns/Column[1]$ as used in this sample MP).

<Configuration p1:noNamespaceSchemaLocation="C:\Documents and Settings\Administrator\Local Settings\Temp\DBProbe – System.OleDbProbe.xsd" xmlns:p1="http://www.w3.org/2001/XMLSchema-instance">
  <ConnectionString>Provider=SQLOLEDB;Server=.;Database=MyAppDB;Trusted_Connection=Yes</ConnectionString>
  <Query>
  –First get the most-recent-row in the MessageLog table.  Only read up to this in case there are writes during reads.
  –The query will pick up any late-written rows on the next poll

  declare @LastRow bigint
  select @LastRow = max([ID]) from MessageLog

  –Do the select.  The column names are not friendly, until you understand how these columns are referenced
  –by downstream workflow modules.  There is no way to refer to columns by name, only by sequence number.

  select
    [EventCode] as [1],
    isnull([User],’n/a’) as [2],
    isnull([Computer],’n/a’) as [3],
    [Severity] as [4],
    [Message] as [5],
    convert(varchar(15),[TimeStamp],106) as [4]
  from MessageLog
  Where
    [ID] &gt; (select MessageLogLastRow from DBMPPersistence)
  and
    [ID] &lt;= @LastRow
  Update DBMPPersistence set MessageLogLastRow = @LastRow
  </Query>
  <GetValue>true</GetValue>
  <OneRowPerItem>true</OneRowPerItem>
</Configuration>

The OleDB module returns a RecordSet in XML form.  RecordSets can have zero records, and indeed that’s what you’ll have if there were no rows to read.  It’s worth putting in an ExpressionFilter module to filter for this condition so that the datasource itself returns no data at all, rather than an empty recordset.

 

You can also optionally add a Mapper module to convert the OleDB data returned from the above module into something that looks more like an event.

 

 

Consuming Data

The rest of the work fans out quickly.  You can create a basic rule to read from the datasource and filter for values.

where the filter expression uses relatively familiar Event terms thanks to the Mapper:

You could also drill into the OleDBData dataitem that gets returned and embedded in the event.  Remember, the syntax for this is by column number:

$Data/EventData/DataItem/Columns/Column[2]$ would give you the username.  (in the XPathQuery tag of an ExpressionFilter, you don’t use the dollar signs or the leading "Data" tag, in other words filtering for username would just have

<XPathQuery>EventData/DataItem/Columns/Column[2]</XPathQuery>

)

You can also do more interesting things like create MonitorTypes and look for state based on information in a database..

At this point, there are too many screenshots to post here.  See the MP for the MonitorType definition and a couple of example uses.

Note that, using the DiscoveryMapper module, you can also do discovery of classes where the information is stored in a database.

 

Caveats and Gotchas

RunAs assignment

Remember, when you import your MP, you must create a RunAs account, assign it to the profile, and associate the profile with the agent where the SQL Query will run (where the rule is targeted).  Otherwise you’ll see this:

Event Type:    Warning
Event Source:    Health Service Modules
Event Category:    None
Event ID:    11852
Date:        6/17/2008
Time:        9:58:23 AM
User:        N/A
Computer:    SERVER1
Description:
OleDb Module encountered a failure 0x80040e09 during execution and will post it as output data item. 

Workflow name: DBMP.SimpleDBRule
Instance name: server1.manage.com
Instance ID: {093355C8-0283-EED8-A6BB-393E82B1FA19}
Management group: OM-MG

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

 

Persistence conflicts

The MP built above creates an environment where there is one common data provider that feeds information to all OpsMgr workflows that look at that data.  There will be one query run against the database every $IntervalSeconds$, and the rows returned will be processed as dataitems by each consuming workflow.

If you have rules or monitors running at different intervals, or do anything that results in multiple instances of the DataSource itself remember to create different rows in the persistence table.  Otherwise you will have an issue where your once-every-minute workflow reads the table and updates the LastReadRow value, then your once-every-10-minutes workflow will come along and only read since the LastReadRow, in other words it will miss 9 minutes of data.  You can avoid this by not using $IntervalSeconds$ as a parameter and just hard-coding the interval in the Datasource workflow.

Fields and Datatypes

You can get into a lot of trouble with very cryptic errors if you don’t do exactly the right things with your query.  These right things aren’t documented.  Good luck.  Some issues to look out for:

  • cast or convert Date/Time fields as nvarchar
  • watch for nulls and convert to strings.  To do this use select isnull(fieldname,”) from dbname syntax
  • cast TEXT fields as nvarchar (Select cast(fieldoftext as nvarchar (500)) as [1] from dbname)
  • Be liberal with use of rtrim to trim whitespace off the end of values
  • use <[CDATA[  ]]> wrappers around your query text in the MP XML to make them more readable and copy/paste-able between SQL Query Analyzer and the MP XML editor
  • Event log errors indicating 0x80040e4d mean that you don’t have your ConnectionString exactly and perfectly right, and there’s no documentation for which OleDB syntax to use.  I’m saving you hours just by telling you about this, even without telling you how to write your ConnectionString.  This event looks like

    Event Type:    Warning
    Event Source:    Health Service Modules
    Event Category:    None
    Event ID:    11852
    Date:        6/16/2008
    Time:        1:06:23 PM
    User:        N/A
    Computer:    SERVER1
    Description:
    OleDb Module encountered a failure 0x80040e4d during execution and will post it as output data item. 

    Workflow name: DBMP.CollectAllDBEntries
    Instance name: server1.manage.com
    Instance ID: {093355C8-0283-EED8-A6BB-393E82B1FA19}
    Management group: OM-MG

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

UPDATE – additional gotchas I just found (4/1/09 – no joke)

  • Event log 0x80040e14 indicates a syntax error in the SQL query itself.  If you’re using CDATA tags you can easily copy and paste the query from your MP into a SQL Query Analyzer to get to the root of it.  In my case, I was missing a closing parenthesis on a Cast call
  • Event log Error 1000’s are hard to troubleshoot because whatever broke is buried in SCOM source code, not in yours.  The SQL query runs just fine, just not from within the MP.  In my case, the following error (which had the same Stamp, but different fault addresses at different times) was because I had used a substring() without wrapping it with a cast().  The solution was cast(substring(blah, 1, 200) as varchar(200))

    Event Type:    Error
    Event Source:    HealthService
    Event Category:    None
    Event ID:    1000
    Date:        4/1/2009
    Time:        11:41:45 AM
    User:        N/A
    Computer:    HORIZONDEMO
    Description:
    Faulting application monitoringhost.exe, version 6.0.6278.0, stamp 47b71437, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x029f72f8.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    Data:
    0000: 41 00 70 00 70 00 6c 00   A.p.p.l.
    0008: 69 00 63 00 61 00 74 00   i.c.a.t.
    0010: 69 00 6f 00 6e 00 20 00   i.o.n. .
    0018: 46 00 61 00 69 00 6c 00   F.a.i.l.
    0020: 75 00 72 00 65 00 20 00   u.r.e. .
    0028: 20 00 6d 00 6f 00 6e 00    .m.o.n.
    0030: 69 00 74 00 6f 00 72 00   i.t.o.r.
    0038: 69 00 6e 00 67 00 68 00   i.n.g.h.
    0040: 6f 00 73 00 74 00 2e 00   o.s.t…
    0048: 65 00 78 00 65 00 20 00   e.x.e. .
    0050: 36 00 2e 00 30 00 2e 00   6…0…
    0058: 36 00 32 00 37 00 38 00   6.2.7.8.
    0060: 2e 00 30 00 20 00 34 00   ..0. .4.
    0068: 37 00 62 00 37 00 31 00   7.b.7.1.
    0070: 34 00 33 00 37 00 20 00   4.3.7. .
    0078: 69 00 6e 00 20 00 75 00   i.n. .u.
    0080: 6e 00 6b 00 6e 00 6f 00   n.k.n.o.
    0088: 77 00 6e 00 20 00 30 00   w.n. .0.
    0090: 2e 00 30 00 2e 00 30 00   ..0…0.
    0098: 2e 00 30 00 20 00 30 00   ..0. .0.
    00a0: 30 00 30 00 30 00 30 00   0.0.0.0.
    00a8: 30 00 30 00 30 00 20 00   0.0.0. .
    00b0: 66 00 44 00 65 00 62 00   f.D.e.b.
    00b8: 75 00 67 00 20 00 30 00   u.g. .0.
    00c0: 20 00 61 00 74 00 20 00    .a.t. .
    00c8: 6f 00 66 00 66 00 73 00   o.f.f.s.
    00d0: 65 00 74 00 20 00 30 00   e.t. .0.
    00d8: 32 00 39 00 66 00 37 00   2.9.f.7.
    00e0: 32 00 66 00 38 00 0d 00   2.f.8…
    00e8: 0a 00                     ..     

 

http://cid-f2cbe6bae5bb51e1.skydrive.live.com/embedrowdetail.aspx/Public/DBMP.zip

Advertisements
This entry was posted in Management Packs. Bookmark the permalink.

One Response to Reading a database table like a log file

  1. Graham says:

    Brilliant. Thanks Mike. We\’d been looking at doing this via timed script monitor – VBS – ADO.DB but this is far more elegant.

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