I want the output of my SQL Stored Procedure to show multi channels in one sensor, instead of having a single sensor for every value that I want to monitor.

How can I do this?

Article Comments

PTF.SQLspXML

This can done with Custom Sensor PTF.SQLspXML

The sensor parses the multi channel results of a SQL query to PRTG.

Parameters:

-d=database -sp=StoredProcedure [-s=SQLserver] [-u=username] [-p=password|passhash]
 
-d=    Database name on the SQL server.
-sp=   Name of the Stored Procedure to execute.
-s=    Optional, IP or DNS name of the SQL server.
       If ommitted the hostname of the parent device is used
-u=    Optional, UserName of the SQL account.
       If ommitted the credentials entered at the parent device are used.
-p=    Optional, Password or PassHash * of the SQL account.
       If ommitted the credentials entered at the parent device are used

The Stored Procedure has to return columns with a fixed name:

CREATE PROCEDURE [dbo].[spSQL2XML]
AS
BEGIN
	DECLARE @tblPRTG	TABLE
		(
		  Channel	nvarchar(50)
		, Value		nvarchar(50)
		, IsInt		bit
		, Unit		nvarchar(50)
		);
		
	INSERT INTO @tblPRTG VALUES ('Channel #1','1.00',0,'Euro')
	INSERT INTO @tblPRTG VALUES ('Channel #2','2.00',0,'Dollar')
	
	SELECT * FROM @tblPRTG
END

Note: this sensor is of the EXEXML type and requires PRTG version 8.2.0.1863 or later.


Feb, 2011 - Permalink