Home » System Center Operations Manager 2007 » Query a database with a monitor – part two

Query a database with a monitor – part two

By in System Center Operations Manager 2007 on .

In this post I wrote about a script that you can use to query a databas. The script in this post will count number of fields, if there are more then four, status of the monitor will be changed. It will also include number of rows in the alert description. I use the following settings on the monitor, a scripting/timed script two state monitor. In the following example I have a database on my root management server. The database is named TechDaysdb and includes a table named OpsMgrV. The name of the root management server is mobile-opsmgr.

  • General
    • Name: Contoso – query db monitor
    • Monitor Target: for Root Management Server
    • Management Pack: TechDays demo
  • Schedule
    • Run every 2 Minutes
  • Script
    • File Name: TechDaysmonitor.vbs
    • Script: see below
  • Unhealthy Expression
    • Property[@Name=’Status’] does not contain Ok
  • Healthy Expression
    • Property[@Name=’Status’] contain Ok
  • Health
    • Healthy Healthy Healthy
    • Unhealthy Unhealthy Warning
  • Alerting
    • check Generate alerts fort this monitor
    • check Automatically resolve the alert when the monitor returns to a healthy state
    • Alert Name: TechDays – db monitor
    • Alert Description: There are to many rows in the database. There are $Date/Context/Property[@Name=’Rows’]$ rows.

 

Const adOpenStatic = 3

Const adLockOptimistic = 3
Set oAPI = CreateObject(“MOM.ScriptAPI”)
Set oBag = oAPI.CreatePropertyBag()
Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
objConnection.Open _
“Provider=SQLOLEDB;Data Source=mobile-opsmgr;” & _
“Trusted_Connection=Yes;Initial Catalog=TechDaysdb;” & _
“User ID=domain\username;Password=password;”
objRecordSet.Open “SELECT * FROM OpsMgrV”, _
objConnection, adOpenStatic, adLockOptimistic
varNo = objRecordSet.RecordCount
If varNO > 4 Then
Rows = varNo
Call oBag.AddValue(“Status”,”Error”)
Call oBag.AddValue(“Rows”,Rows)
Call oAPI.Return(oBag)
Else
Call oBag.AddValue(“Status”,”Ok”)
Call oAPI.Return(oBag)
End If

14 Comments

  3. I’d like to see this example leveraging the Default SQL Monitoring Run As account instead of embedded credentials. Script embedded credentials would absolutely not be allowed in my environment.

  4. Seems like you agent action account NY Aithority\system dont have permissions to the SQL database you are running the query against. Either you can use a run as profile to set which account to do the check with or you need to give the agent action account more permissions.

  5. I tried the script but modified it for my requirement but got below warning event in the event log.

    The process started at 12:38:27 failed to create System.PropertyBagData. Errors found in output:

    C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 2\8009\serverisnotoperational.vbs(21, 1) Microsoft OLE DB Provider for SQL Server: Login failed for user ‘NT AUTHORITY\SYSTEM’.

    Command executed: “C:\Windows\system32\cscript.exe” /nologo “serverisnotoperational.vbs”
    Working Directory: C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 2\8009\

  6. […] I wrote a SQL query that I thought I could share. This SQL query will show collected performance data for a specific machine for the last 30 minutes. I was working with a issue where some agents stopped sending performance data. Before we found the root cause and a fix, we configured this script in a monitor to give us an alert if the agent was not sending performance data. Info how to configure a monitor to run a SQL query can be found here. […]

  9. Hi Anders,

    Just a quick note to say that your blog is fantastic, its very informative 😉

    I’ve got a question which i’m hoping you can help me with, to which i’ve been struggling to find much information on crawling the net.

    I’m running SCOM2012 RC1, with a custom rule setup to execute a command shell on a redhat linux 5 box, the target is the rhel computer group, the runas account is all good (currently root), the command is as follows: grep error /root/SCOM_Log.txt && echo “Red”

    SCOM_Log.txt simply has one line in it: error

    The above command should return an exit code of 1 and raise an alert via the rule, but for some reason, nothing ever happens; the command executes perfectly fine on the rhel console itself (checking for red in the logfile confirms this) however when checking the logfile after the rule should have gone off, nothing has changed.

    Is there a better way of parsing a logfile in SCOM for a Linux LogFile which supports multiple lines?

    Any advice you can give would be greatly appreciated
    Regards
    Andrew Brinded

  10. Hi Anders,

    Just a quick note to say that your blog is fantastic, very informative 😉

    I’ve got a question i’m hoping you can help me with, to which i’ve been struggling to find much information on crawling the net.

    I’m running SCOM2012 RC1, with a custom rule setup to execute a command shell on a redhat linux 5 box, the target is good, the runas account is all good,

  12. Actually, you should change alert like this: $Data/Context/Property[@Name=’Rows’]$ rows
    Not %Date

  14. Nice post. I tried this, but the alert doesn’t seem to show the information.

    Here’s how it shows:

    Alert description: $Date/Context/Property[@Name=’Rows’]$ ROWS. There are to many rows in the database.

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.