Multi-channel from SQL query

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.