New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


Multi-channel from SQL query

Votes:

0

Your Vote:

Up

Down

I have a simple SQL query that returns a table with two columns. Column 1 is the name and column two is the result (0=Success, 1=Warning, 2=Fail).

Is it possible to monitor this table with a single SQL query? Additional rows may be added over time so I would like to do this dynamically rather than having to add a new sensor every time.

sensor sql table

Created on Oct 19, 2015 1:31:52 AM by  mhouston100 (170) 1 2



2 Replies

Accepted Answer

Votes:

5

Your Vote:

Up

Down

For those interested, I ended up with a Powershell script to create an XML output to push to a custom EXE/XML advanced sensor.

Once I wrapped my head around the format of the output, it wasn't difficult.

Not sure how code looks in here but the script was for parsing the Veeam database and finding the last results of the backups, then feeding the table to the sensor.

param(
    [string] $serverInstance,
    [string] $sourceDatabase
)

function Invoke-SQL {
    param(
        [string] $dataSource = ".\SQLEXPRESS",
        [string] $database = "MasterData",
        [string] $sqlCommand = $(throw "Please specify a query.")
      )

    $connectionString = "Data Source=$dataSource; " +
            "Integrated Security=SSPI; " +
            "Initial Catalog=$database"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataSet) | Out-Null

    $connection.Close()
    $dataSet.Tables

}

$query = "SELECT [name],[latest_result] FROM [$sourceDatabase].[dbo].[BJobs]"

$backupResults = Invoke-SQL -dataSource $serverInstance -database $sourceDatabase -sqlCommand $query



$xmlOutput = '<?xml version="1.0" encoding="UTF-8" ?><prtg>'

foreach ($result in $backupResults) {
    $xmlOutput = $xmlOutput + "<result><channel>$($result.name)</channel><value>$($result.latest_result)</value><LimitMaxWarning>1</LimitMaxWarning><LimitMaxError>2</LimitMaxError><LimitMode>1</LimitMode></result>"
}

$xmlOutput = $xmlOutput + "</prtg>"

$xmlOutput #| Out-File c:\temp\out.xml

You can then create an EXE/XML Advanced sensor, run this script with the parameters:

-serverInstance (servername) -sourceDatabase (databasename)

and it will create a channel for each backup job with success, warning and error levels.

Created on Oct 19, 2015 9:09:24 PM by  mhouston100 (170) 1 2



Votes:

1

Your Vote:

Up

Down

Hi Matthew,

Thank you very much for sharing your script. I'm sure this will be helpful to other users! Did you already try the SQLv2 sensor? This sensor will also be support multi channels for SQL queries.

Best regards, Felix

Created on Oct 20, 2015 5:26:11 AM by  Felix Saure [Paessler Support]

Last change on Oct 20, 2015 6:28:55 AM by  Felix Saure [Paessler Support]



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.