SQL Query into a Rule (via PowerShell) > then into Squared Up

PowerShell
, , ,
1

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)

2

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.

1 Like
3

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.

4

Thanks for coming back,

I guess id like to explore both options, but yes, over time (say a week) would be best.

With regards to the SQL plugin, id like to know more as I could potentially use this for other parts of the system. Is there any documentation about this plugin covering how to install and what it can do?

5

http://support.squaredup.com/support/solutions/articles/206748-how-to-configure-the-sql-plugin