Solved SQL Query into a Rule (via PowerShell) > then into Squared Up
Hi all, I would like to run a SQL query against my db which returns a list of rows with a value against each row (sample below).
I assume this is done via a vbs script embedded into a Rule run every x mins. The values inserted into property bags, then returned.
I have been able to return a single value from a rule and view its change over time (performance), however getting multiple values into the property bag and returning them ive been unable to do. any help greatly appriciated.
apples 20
oranges 25
pears 3
Script sample below, key server names removed
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=prov;Data Source=db;” & _
“Initial Catalog=IC;”
objRecordSet.Open “SQL query here”, _
objConnection, adOpenStatic, adLockOptimistic
apples = objRecordSet.Fields.Item(0)
objRecordSet.MoveNext
oranges = objRecordSet.Fields.Item(0)
objRecordSet.MoveNext
pears = objRecordSet.Fields.Item(0)
apples = apples
oranges = oranges
pears = pears
Call oBag.AddValue(“apples”,apples)
Call oBag.AddValue(“oranges”,oranges)
Call oBag.AddValue(“pears”,pears)
Call oAPI.Return(oBag)
Best answer
You actually have a couple of options here.
Squared Up released a PowerShell monitoring management pack that will allow you to create monitoring using PowerShell, anywhere in the authoring pane that you previously saw VBScript.
https://download.squaredup.com/management-packs/powershell-monitoring-management-pack/
Zvi also created a similar MP that utilizes SQL queries.
https://squaredup.com/blog/a-new-free-management-pack-to-create-scom-monitors-from-any-sql-query/
You now have the ability to do it however you like, with relative ease.
Just to help clarify it for me, are you wanting this in a rule so you can collect the data over time? If not, you could use the SQL Plugin to pull the data into a dashboard.
If you are wanting the data over time, it might be best to have each one as its own metric and collect them individually to display the values over time.
Answer this question
To reply or comment, use the 'Comment' link on the relevant answer or question.
