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

MSSQL Sensor Input Parameter

Votes:

0

Hi, I would create an generell SQL Script with following query:

select sum(state + user_access + is_read_only + is_in_standby) as Status
from sys.databases
where name in ('master', 'tempdb', 'DB1', 'DB2')

Now I have used the placeholder @prtg in the query but it didn't work

select sum(state + user_access + is_read_only + is_in_standby) as Status
from sys.databases
where name in (@prtg)

On the Sensor Settings: Input Parameter: 'master', 'tempdb', 'DB1', 'DB2'

Now I geht following back: Id 3 is [DBNull]

When I only type master or DB1 in the Input Parameter it works. But I want to type more values in the paramater field.

parameters prtg sql-v2

Created on Mar 30, 2018 8:38:43 AM

Last change on Apr 2, 2018 9:24:21 AM by Felix Saure [Paessler Support]



4 Replies

Votes:

0

Hello Ronald,

Thanks for your contacting us. I'm afraid that it's just possible to forward a single variable and no array to the SQL sensors, sorry. If you want to add up the results from different databases, you can either use a Sensor Factory Sensor, or create different .sql files for PRTG which includes the desired names.

Best regards, Felix

Created on Apr 2, 2018 9:38:02 AM by Felix Saure [Paessler Support]



Votes:

0

Hi Ronald, not sure if it still helps you but maybe someone else does. I did solve it using parameters in xml like format (cant use < and > for obvious reasons). Then in my sql I extracted the variables. Here is a sample of sql file for SQL v2 sensor

DECLARE @input varchar(200)
DECLARE @a int
DECLARE @b int
DECLARE @c int
DECLARE @xml xml
SET @input = @prtg
SET @input = REPLACE ( @input , '{' , '<' ) 
SET @input = REPLACE ( @input , '}' , '>' ) 
SET @xml = CAST(@input AS XML)

SET @a = @xml.value('(/group/@a)[1]', 'int')  
SET @b = @xml.value('(/group/@b)[1]', 'int')  
SET @c = @xml.value('(/group/@c)[1]', 'int')  

select @a,@b,@c

Br, Karol

Created on Feb 11, 2019 7:42:45 PM

Last change on Feb 11, 2019 7:42:45 PM



Votes:

0

Thanks for sharing, Karol! :)

PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Feb 11, 2019 7:47:02 PM by Stephan Linke [Paessler Support]



Votes:

0

Karol,

Hi can you please confirm the format of the input parameter on the sensor settings?

This Input Parameter is showing a Timeout Error and the @PRTG variable in the SQL script is not receiving the xml string.

{val duration="1" messageType="2" /}

Thanks, JW

Created on Aug 18, 2022 4:47:18 AM




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.