What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

PRTG Stored Procedure sensor

Votes:

0

I want to test some functionality on an SQL Database with result 0 (succes) or 1 (false) But i cannot get the returncode of the SP interpreted. I stumbled upon this half a year ago also , and back then i just created a workaround by running the exact code of the SP as a query. However i rather call a SP so noone can do funny things with the code that is stored locally on the prtg machine.

Is there a way that i can test simply if a 0 or 1 is returned from a SP ?

return-code sensor sql

Created on Jun 27, 2017 11:16:50 AM



Best Answer

Accepted Answer

Votes:

1

I finally got it to work :)

For people wanting to create the same functionality of a simple return value out of a Stored Procedure i created the steps in a logical order, providing them an easier way to get stuff working :

Make sure your SP returns a 0 or 1 by filling a variable with a 0 for OK or a 1 for Error, using Select @NameOfVariable as the last command before end in your SP

Create an SQL query file :

C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mssql\<NameOfSqlSensorFile.sql>

in the file include a single line : 

exec <nameofStoredProcedure>

Create an lookup file on the PRTG host :

C:\Program Files (x86)\PRTG Network Monitor\lookups\custom\prtg.yes0no1.ovl

containing : -------------------------

<?xml version="1.0" encoding="UTF-8"?>
  <ValueLookup id="prtg.yes0no1" desiredValue="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd" undefinedState="Warning">
    <Lookups>
      <SingleInt state="Ok" value="0">
        Ok
      </SingleInt>
      <SingleInt state="Error" value="1">
        Error
      </SingleInt>
    </Lookups>
  </ValueLookup>

Rescan the lookup files (PRTG Console, setup, System Administration, Administrative tools, reload lookups)

Add sensor, Database, type Microsoft SQL v2

Sensor name : <NameOfSensor>

Database : <NameOfDatabase>
SqlQueryfile : Select the created SqlQueryFile <NameOfSqlSensorFile.sql>, it should be listed in the pulldown.
Data Processing : Process data table (if not selected now it cannot be selected later)
Select channel value by : Row Number
Sensor channel #1 name : Status
Sensor channel #1 row number : 0
Sensor channel #1 unit : Value Lookup
Sensor channel #1 Value Lookup : prtg.Yes0No1

All other values can be left at their default

Created on Jul 3, 2017 11:41:35 AM

Last change on Jul 3, 2017 12:03:45 PM by Luciano Lingnau [Paessler]



8 Replies

Votes:

0

You should be able to invoke the stored procedure within your SQL script. That sholdn't be a problem. Please refer to this set-up guide:

Once you get the sensor working and reading the output of 1 or 0, it's time to create a so-called lookup:

The lookup can be used to display a user friendly state in the sensor like "Backup is working" or "Database is Optimal" and adjust the sensor's state/status accordingly.

Please let me know how that works out.

Luciano Lingnau
[Paessler Support]

Created on Jun 28, 2017 12:29:48 PM by Luciano Lingnau [Paessler]



Votes:

0

Hi Luciano

the problem is , i do not need a friendly state, green or red is enough for me , there are no other values than 0 or 1 ... However the monitor stays always green , if i force a return code of 0 or 1 it still stays green so it looks like the value given back from the executed sp does not get recognized.

i create a sql script , which simply states exec NameOfSP does the monitor interpret the ability to execute the SP as being succesful perhaps instead of getting back the result.

I've been reading the guides several times and in the end i turned here hoping someone can help me.

I have 6-7 sql monitors that execute a query and the result gets interpreted correctly, however when i try to do a SP which only returns a 0 or 1 it doesn't work.

Created on Jun 28, 2017 12:37:21 PM



Votes:

0

Do you see the value of 0 or 1 in the sensor's overview?

Does the procedure always return data rows? Are you able to share the output of the result when you run it interactively?

Please note that the sensor explicitly needs to be configured to enable the "Data Processing". This can only be done when the sensor is recreated and will allow you to configure PRTG to read a specific result from the output of the command, which in turn will be displayed in the channel. (Exactly as pictured in the guide).

Best Regards,
Luciano Lingnau [Paessler Support]

Created on Jun 28, 2017 1:41:50 PM by Luciano Lingnau [Paessler]



Votes:

0

I created a new sensor entry Test

  • set data processing to : Process data Table
  • sensor channel #1 name RETURN
  • sensor channel #1 COLUMN NUMBER

No idea how to set the sensor channel #1 unit , tried count, lookup value, custom etc etc

Sensor turns red with ''No Valid datatable was returned''

A piece of the Stored procedure that i call

	-- Alert if  Added Total = 0, else update OrderCount Table

	DECLARE @RetVal int;

	IF @AddedTotal = 0
		SET @RetVal = 1;
	ELSE
		BEGIN
			SET @RetVal = 0;
		
			-- Update OrderCount Table 

			EXEC [dbo].[OrderCountUpdate]
		END

	RETURN @RetVal

the SP will return a 0 or a 1 depending on the number of orders since last interval.

(i forcedly returned a 1 or 1 to test the functionality, it always stays green)

Created on Jun 28, 2017 2:02:35 PM

Last change on Jun 28, 2017 2:21:50 PM by Luciano Lingnau [Paessler]



Votes:

1

Have you already tried replacing RETURN @RetVal with SELECT @RetVal

Created on Jun 29, 2017 1:50:10 PM



Votes:

0

Hello eviled,

Please make sure that you're also configuring the sensor to read the output of the query, it needs to be assigned to a channel as instructed here.

If that doesn't work out, we'll need some more information to understand the issue:

  • Screenshot from the sensor's overview
  • The sensor's configuration report (Generated within the settings tab using the small printer icon at the bottom of the page, save as PDF)
  • Enable the "Log result to disk" option within the sensor's settings, and share the logs that you'll find under "C:\ProgramData\Paessler\PRTG Network Monitor\Logs (Sensors)"

Best Regards,
Luciano Lingnau [Paessler Support]

Created on Jun 30, 2017 8:12:17 AM by Luciano Lingnau [Paessler]



Votes:

0

I will check the select @retval solution later on , i solved my problem by creating a workaround which is basically the same as the suggested solution but then with a lot of hassle :p

the stored procedure now writes the result in a monitoring table together with date and time and some optional fields. i then query the result from that table.

Now it occurs to me that PRTG simply cant handle the ''return function and value" but that it wats the result of a select statement. This is weird (for me) since i always used the stored procedure's return function as described (with other monitoring software)

If the proposed select @retval does work, the functionality is identical.

perhaps this would be something to add a little clearer to the faq :

if you have a stored procedure wich you want to give back a simple true/false status to a sensor make sure you fill a variable with the status and end the stored procedure with a select statement on that variable.

Created on Jul 3, 2017 9:34:08 AM



Accepted Answer

Votes:

1

I finally got it to work :)

For people wanting to create the same functionality of a simple return value out of a Stored Procedure i created the steps in a logical order, providing them an easier way to get stuff working :

Make sure your SP returns a 0 or 1 by filling a variable with a 0 for OK or a 1 for Error, using Select @NameOfVariable as the last command before end in your SP

Create an SQL query file :

C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mssql\<NameOfSqlSensorFile.sql>

in the file include a single line : 

exec <nameofStoredProcedure>

Create an lookup file on the PRTG host :

C:\Program Files (x86)\PRTG Network Monitor\lookups\custom\prtg.yes0no1.ovl

containing : -------------------------

<?xml version="1.0" encoding="UTF-8"?>
  <ValueLookup id="prtg.yes0no1" desiredValue="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd" undefinedState="Warning">
    <Lookups>
      <SingleInt state="Ok" value="0">
        Ok
      </SingleInt>
      <SingleInt state="Error" value="1">
        Error
      </SingleInt>
    </Lookups>
  </ValueLookup>

Rescan the lookup files (PRTG Console, setup, System Administration, Administrative tools, reload lookups)

Add sensor, Database, type Microsoft SQL v2

Sensor name : <NameOfSensor>

Database : <NameOfDatabase>
SqlQueryfile : Select the created SqlQueryFile <NameOfSqlSensorFile.sql>, it should be listed in the pulldown.
Data Processing : Process data table (if not selected now it cannot be selected later)
Select channel value by : Row Number
Sensor channel #1 name : Status
Sensor channel #1 row number : 0
Sensor channel #1 unit : Value Lookup
Sensor channel #1 Value Lookup : prtg.Yes0No1

All other values can be left at their default

Created on Jul 3, 2017 11:41:35 AM

Last change on Jul 3, 2017 12:03:45 PM by Luciano Lingnau [Paessler]




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.