Monitor SQL table for errors

Dashboard Server
1

Hi,

I need to monitor a SQL table for any new status failure type descriptions and then raise a SCOM alert.

Abit of background, SCORCH is running some runbooks and then stores the messages in a sql table.

Any ideas on how to do this ?

Cheers

Saj

2

You have a couple of options here.

  1. Use the inbuilt OLE DB monitoring template:

https://docs.microsoft.com/en-us/previous-versions/system-center/system-center-2012-R2/hh457575(v=sc.12)

  1. Use the Community PowerShell monitoring management pack, alongside the Microsoft SQL module

https://download.squaredup.com/management-packs/powershell-monitoring-management-pack/

3

ok thanks. The Powershell monitoring MP looks good. Any chance someone can provide a powershell script to connect to sql and pull out the error messages from the table ?

4

Something like this should work.
Just modify the query to get the results you want.

$ScomAPI = New-Object -comObject “MOM.ScriptAPI”
$propertybag = “”
$PropertyBag = $ScomAPI.CreatePropertyBag()
$errorhash = “”
$errorhash = Invoke-Sqlcmd -Query "select errorfieldname from errortablename where errorfieldstatusname=‘Error’ -ServerInstance “sqlservername” -Database “errordatabase”

if ($errorhash -ne “”)

{
$PropertyBag.AddValue(“State”,“Error”)
$PropertyBag.AddValue(“MessageText”,$errorhash)
}

else{
$PropertyBag.AddValue(“State”,“Ok”)
}

$PropertyBag

 

You can also use this managementpack:

https://github.com/UretzkyZvi/Monitor-Applications-Using-SQL-Queries

If you only wanna raise an alert if there is error messages. You wont see the messages in question but you will get an alert.

5

ok thanks guys, really appreciate this.

6

Used the below script and then created a SCOM alert rule based on powershell script. I do not get any data back with details of error message. any ideas ?

 

$ScomAPI = New-Object -comObject “MOM.ScriptAPI”
$propertybag = “”
$PropertyBag = $ScomAPI.CreatePropertyBag()
$errorhash = “”
$errorhash = Invoke-Sqlcmd -Query “select top 1 * from [dbo].[ACTIVITY_TRACE] where Status = ‘failed’ order by ID desc” -ServerInstance “SCOMServer” -Database “PDS”

if ($errorhash -ne “”)

{
$PropertyBag.AddValue(“State”,“Error”)
$PropertyBag.AddValue(“MessageText”,$errorhash)
}

else{
$PropertyBag.AddValue(“State”,“Ok”)
}

$PropertyBag

7

I do get an alert in SCOM but the description part of it is blank. Have used $Data/Context/DataItem/Property[@Name=‘MessageText’]$ and also tried $Data/Context/Property[@Name=‘MessageText’]$.

I know my sql command works as I have tested this on my scom server where sql is also installed.

8

You mention that you created a SCOM alert rule? Did you mean monitor or is this genuinely a rule?

Are you able to export the xml and sanitise in a way that you don’t expose any sensitive information? If so, post it up here and we can take a look.

If it is a monitor (and that would make sense as you have healthy and unhealthy states) then I would start by hard coding the MessageText e.g.

$PropertyBag.AddValue(“MessageText”,“An error occurred”)

Just to make sure it isn’t some quirk of whatever is returned as $error-hash

$Data/Context/Property[@Name=’MessageText’]$ should then work.

Cheers

Graham

9

you may also find this thread of interest - https://community.squaredup.com/answers/question/orchestrator-runbook-jobs-failures-rule/

10

Do you get any alert at all? Does the account that runs the powershellscript have access to the database?
On the alerting tab have you included the following line:
$Data/Context/DataItem/Property[@Name=‘MessageText’]$

Run the powershellscript as your user and see if you get a result back.