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

SQL sensor Input Parameter

Votes:

0

Hi. I have simple SQL query: USE BEDB SELECT TOP 1 * FROM dbo.JobHistorySummary WHERE JobName = 'dbc02 BPM-Incremental' and IsJobActive not IN ('1') ORDER BY EndTime DESC

I need to change the name of the work (JobName) for each sensor. If I understand correctly I need to write such a request: USE BEDB SELECT TOP 1 * FROM dbo.JobHistorySummary WHERE JobName = '@prtg' and IsJobActive not IN ('1') ORDER BY EndTime DESC

And when you create a sensor in the "Input Parameters" transfer "dbc02 BPM-Inkremental" to the job name is substituted into the query.

SQL

With these settings in the logs in the variable @prtg not contain the value "Input Parameters"

Log: 24.10.16 10:55:58: Running Command 'USE BEDB SELECT TOP 1 * FROM dbo.JobHistorySummary WHERE JobName = @prtg and IsJobActive not IN ('1') ORDER BY EndTime DESC'

custom-sensor query sensor sql

Created on Oct 24, 2016 8:13:59 AM

Last change on Oct 25, 2016 8:13:56 AM by Luciano Lingnau [Paessler]



3 Replies

Votes:

0

Hello dukedizel,
Thank you very much for your kb-post.

  • The described setup and queries look correct, I would expect this to be working. May I ask that you clarify which precise issue you're encountering?
  • The @prtg placeholder will not be replaced in the sensor's log. This is a log for a working sensor (Querying MySQL) for reference: 
[...]
25.10.16 10:09:11: Running Command 'select idorder,value from test.order where customer = @prtg'
[...]
25.10.16 10:09:11: Execution Time: return 93,7209 [Double]
25.10.16 10:09:11: Query Execution Time: return 0 [Double]
25.10.16 10:09:11: Affected Rows: return 1 [Int64]
25.10.16 10:09:11: Id 3: 3 [Int32]
25.10.16 10:09:11: Id 3: return 3 [Int64]
25.10.16 10:09:11: Id 4: 50 [Int32]
25.10.16 10:09:11: Id 4: return 50 [Int64]
  • Please also confirm, which version of PRTG and what SQL "flavor" are you running? Kindly note that different SQL "flavors" use slightly different SQL placeholders which will be replaced by an input parameter:
Microsoft SQL@prtg
MySQL@prtg
PostgreSQL@prtg
Oracle SQL:prtg
ADO SQL? (question mark)



Best,
Sebastian Kniege [Paessler Support]

Created on Oct 25, 2016 8:41:54 AM by Luciano Lingnau [Paessler]

Last change on Oct 25, 2016 8:45:04 AM by Sebastian Kniege [Paessler Support]



Votes:

0

Hello Sebastian, I found a mistake! when I wrote a request I have used quotation marks. I removed them from the query and it worked.

Invalid query:

USE BEDB SELECT TOP 1 * FROM dbo.JobHistorySummary WHERE JobName = **'**@prtg**'** and IsJobActive not IN ('1') ORDER BY EndTime DESC

A valid query:

USE BEDB SELECT TOP 1 * FROM dbo.JobHistorySummary WHERE JobName = **@prtg** and IsJobActive not IN ('1') ORDER BY EndTime DESC

Created on Oct 25, 2016 9:05:19 AM

Last change on Oct 25, 2016 11:55:35 AM by Sebastian Kniege [Paessler Support]



Votes:

0

Dear dukedizel,

Thank you for the information. Glad you found the cause of the error.
Should you have any further questions, please don’t hesitate to contact us.

Best,
Sebastian Kniege [Paessler Support]

Created on Oct 25, 2016 11:58:46 AM by Sebastian Kniege [Paessler Support]




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.