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.

Article Comments

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


Apr, 2018 - Permalink

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


Feb, 2019 - Permalink

Thanks for sharing, Karol! :)

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

Kind regards,
Stephan Linke, Tech Support Team


Feb, 2019 - Permalink

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


Aug, 2022 - Permalink