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'

Article Comments

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]


Oct, 2016 - Permalink

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


Oct, 2016 - Permalink

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]


Oct, 2016 - Permalink