Reading SQL Database and Table and create SCOM Alerts

Reading data from a SQL database table and based on the returned recordset one can easily created SCOM performance counters or SCOM events.

The below VB-Script does just that. It reads a SQL table and for each record it builds up the event message with VBCRLF (Carriage Return Line Feed aka Enter key J).

Dim oAPI

Set oAPI = CreateObject(“MOM.ScriptAPI”)

Set conn = CreateObject(“ADODB.Connection”)’

conn.Open “Provider=SQLOLEDB;Data Source=SQLSERVER;Initial Catalog=Database;Persist Security Info=False;Integrated Security=SSPI”

‘Call oAPI.LogScriptEvent(“ReadSQLTable”, 100, 2, “running”)

conn.commandtimeout = 500

set recset = createobject(“adodb.recordset”)

sql = “SQLSelectStatement”

recset.open sql, conn

Call oAPI.LogScriptEvent(“readsqltable.vbs”, 100, 2, “connected and running”)

recfound = 0

while not recset.eof

eventmsg = eventmsg & vbcrlf & recset.fields(0).value & ” ” & recset.fields(1).value & ” ” & recset.fields(2) & ” ” & recset.fields(3) & ” ” & recset.fields(4)

recset.movenext

recfound = recfound +1

Set oBagAgentVer = oAPI.CreateTypedPropertyBag(1)

call oBagAgentVer.AddValue(“EventID”,912)

call oBagAgentVer.AddValue(“EventSource” ,”ReadSQLTable“)

call oBagAgentVer.AddValue(“EventMessage”,eventmsg)

oAPI.AddItem(oBagAgentVer)

Call oAPI.ReturnItems

Call oAPI.LogScriptEvent(“readsqltable”, 101, 2, eventmsg)

recset.movenext

wend

recset.close

The above script is then put into a Time Command > Execute a Script SCOM rule

For this rule and the access to the particular database I needed to specify a Run-As account

This runas account has a domain user specified with read access to the particular SQL table.

The Scheduler is set to every 1 minute

Because the above script is creating a SCOM event, you then need to configure an Alert Generating Event rule that monitors for the event source and Event Id as specified in the script.

In this case the eventsource is ReadSQLTable and the eventid is 912.

Below is a sample script for reading records out of an Access Database and creating SCOM alerts for the returned recordset

The bold italics parts of the script is what you must substitute the relevant values for your environment.

Dim oAPI

Set oAPI = CreateObject(“MOM.ScriptAPI”)

Set conn = CreateObject(“ADODB.Connection”)

‘Call oAPI.LogScriptEvent(“accessdbforerrors.vbs”, 100, 2, “Run script”)

sconn = “Driver={Microsoft Access Driver (*.mdb)}; DBQ=DirectoryandAccessMBDfilelocation;”

conn.Open sconn

set recset = createobject(“adodb.recordset”)

sql = “AccessSQLSelectQuery

set rs = conn.execute(sql)

‘Call oAPI.LogScriptEvent(“accessdbforerrors.vbs”, 100, 2, rs.recordcount)

evt_msg = “”

while not rs.eof

sss = RS(“Message“)

Call oAPI.LogScriptEvent(“accessdbforerrors.vbs”, 100, 2, sss)

msg = “‘” & sss & “‘”

evt_msg = “Date of error: ” & RS(“fielname”) & ” ” & RS(“time”) & vbcrlf & ” Type: ” & RS(“type”) & vbcrlf & ” Message: ” & msg & vbcrlf

‘ Call oAPI.LogScriptEvent(“accessdbforerrors.vbs”, 100, 2, evt_msg)

Set oBagAgentVer = oAPI.CreateTypedPropertyBag(1)

call oBagAgentVer.AddValue(“EventID”,99101)

call oBagAgentVer.AddValue(“EventSource” ,”EventSource“)

call oBagAgentVer.AddValue(“EventMessage”,evt_msg)

oAPI.AddItem(oBagAgentVer)

rs.movenext

wend

Call oAPI.ReturnItems

Advertisements

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