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

How to set up the SQL v2 sensors in PRTG? Is there a guide?

Votes:

0

I have a few SQL servers in my environment and would like to monitor the results of specific queries using PRTG. Can I use it for alerting and notifications? What setup steps are required?

custom-sensor guide important mysql mysql-v2-sensor oracle-sql-sensor prtg sql tutorials

Created on Aug 1, 2016 11:25:52 AM by Luciano Lingnau [Paessler]

Last change on Aug 6, 2019 6:59:34 AM by Maike Guba [Paessler Support] (2,404) ●2 ●1



5 Replies

Accepted Answer

Votes:

3

This article applies as of PRTG 19

Configuration of SQL v2 sensors

There are a couple of SQL sensor types available in PRTG:

The setup for all SQL sensors is roughly the same.

In this example, we will cover the MySQL v2 sensor, but the steps apply to the other SQL sensor types as well. This guide will assist you in migrating from the deprecated SQL v1 sensors and in configuring SQL v2 sensors for the first time.

If you have previously used "SQL v1" sensors, check out section 3.3 Polishing that explains how to define limits. This replaces the previous Post-Processing warning and error conditions setting from the SQL v1 sensors.

Step 1: Creating the SQL query

The first step is to create a script that defines the SQL query "What values do you want to monitor?" The sensors will be able to run almost any SQL query, but you need to code the query yourself. You can create valuable dashboards for non-IT departments of your organization, for example.

Examples:

  • How much did my company sell today?
  • What is the average order price?
  • How many customers' logins are active, how many customers are logged in, exist?

The possibilities are infinite, as long as you are able to create an SQL query for it. At this point, you should not worry about the sensor or PRTG. Use the management console, workbench, or an equivalent management tool of your SQL server and only focus on the SQL query. Keep in mind that for best results the following restrictions apply:

  • PRTG can only process numerical values.
  • The query should not take several minutes to run. This may cause instability and overload on both PRTG and the SQL server, depending on the scanning interval.
  • The number of channels or values returned by the sensor should not be more than 10 and the amount of results should be static (that is, always the same number of columns in the result).
  • The sensor is not capable of displaying more than one single string. You may concatenate text from multiple rows in a single string using SQL commands in the query.

For this example, we will work with this simple SQL query:

SELECT count(idorder), sum(value), avg(value), GROUP_CONCAT(customer SEPARATOR ', ') 
from test.order

Step 2: Storing the script

If you have created your script with the SQL query and it runs and works in the SQL editor, it is time to store it to disk so that PRTG can use it. The script (with .sql extension) must be stored on your PRTG server under the following path:

%programfiles(x86)%\PRTG Network Monitor\Custom Sensors\sql\%yoursqlflavor%\

In this example, we work with MySQL, so our script has to be stored in the mysql folder. We name it Custom Example.sql. Here is the full path for reference:

C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mysql

Note: It was previously possible with SQL v1 sensors to directly write the query in the sensor's settings. For security reasons (because it would allow someone to directly modify the query from within the web interface), we now require that the script is stored to disk on the PRTG probe system. This requires access to the operating system of the server running a PRTG probe and improves security. For details, see Why do I have to store SQL sensor queries and custom scripts in files on the probe computer?

The screenshot below illustrates how the result (Step 1+2) will look in your SQL editor. Store the very same query in a text file with .sql extension and copy it to the appropriate folder.


Click to enlarge.

Step 3: Creating the sensor in PRTG

Before proceeding, we strongly recommend that you check that the probe system and your environment meet the sensor's requirements. See the sensor's remarks section in the PRTG Manual.

3.1 Defining the credentials for database sensors

With the SQL v2 sensors, we require that credentials are defined at root, probe, group, or device level. This means that you only have to define the credentials once if you have multiple SQL servers to query with the same credentials. Open the Settings tab of the corresponding object and enter the appropriate SQL credentials in section Credentials for Database Management Systems.

3.2 Creating the sensor

  1. Right-click the device where you want to deploy the sensors, click Add Sensor from the context menu, search for SQL, and select the appropriate sensor version that you require.
  2. In the Add Sensor (Step 2 of 2) dialog, enter the Sensor Name and the name of the Database that the sensor will query. In our example the database name is test and we keep the default sensor's name.
  3. Under the Data section, select the SQL Query File that you saved in Step 1 and define the Transaction approach and if you want PRTG to read or evaluate the results of the query.
  4. Set the Data Processing option to Process data table. For this example, we work with Process data table. After selecting this option, a whole new section will appear.
  5. Now it is time to decide on the channel selection approach. The standard option is the default Column number approach, which means that the multiple results from the query will be assigned to incremental numbers, 0, 1, 2, 3 and so on. Our example script returns 4 rows, so we use Column number for the Select Channel Value by option.
  6. As previously tested and defined, the example script returns the following information from the "order" table.
    • The count of unique orders
    • The sum of the value of all orders
    • The average value of all orders
    • A string that contains the customer names (This is interesting if you want to identify results in a TOP kind query.)

      To assign the results to channels, enable the desired number of channels. Only one channel is enabled by default. Specify the channel's name (that does not have to match the field's name). Because we chose to select the channel by column number, enter the column number that you want to assign to this channel. The first column has the ID 0 (zero). See the "filled in" settings page in the screenshot below:


      Click to enlarge.

      It is not required that you use all results from the query in the sensor and you can also use them in any order.

      Note: If you need to map a specific response value to a sensor status (for example, 1 = OK, 2 = Broken, 3 = Success), you need to use lookups. These should be defined during the sensor's creation (and not via the channel settings later). For further details about lookups, see Value interpretation (aka Lookups).

3.3 Polishing (primary channel, limits)

The previous steps are sufficient to get a working sensor. You can take the following steps to "polish" the created sensor.

  1. Use the pin icon to define your primary channel. The primary channel is somewhat special because its value is displayed in notifications and as a sensor value when browsing the device tree.


    Click to enlarge.
  2. Click a channel to open its settings and use this window to define limits that are very useful for alerting. With such limits, you can set the sensor to a Warning or a Down status if the value of the channel goes above or below a specific threshold.


    Click to enlarge.
  3. In the end, your sensor should look like this:


    Click to enlarge.

Step 4: Debugging

If you encounter any issues, debugging the sensor is possible via the Write sensor result to disk (Filename: "Result of Sensor [ID].txt") option in the sensor's settings. When this option is set, the following logfiles will be stored to disk on the probe where you created the sensor with every sensor scan and will provide debugging information about the sensor's execution.

Default path (on the probe system)%programdata%\Paessler\PRTG Network Monitor\Logs\sensors

Once you are done with debugging, we recommend that you disable the Write sensor result to disk (Filename: "Result of Sensor [ID].txt") option again because it can affect the sensor's performance.

4.1 Files

FilenameContent
Result of Sensor [ID].Data.txtContains a collection of the sensor's parameters and settings.
Result of Sensor [ID].logLogs the connection to the database, settings, timings, and the received replies. This is useful for debugging.
Result of Sensor [ID].txtContains the actual output passed to PRTG, usually the very same error message and the values that you see in the PRTG web interface in the sensor message.

If you encounter issues and the debug files do not tell you anything about the error, you should still keep them if you decide to contact our tech support as they will assist us with troubleshooting.

Related topics

The example below explains the usage of lookups (that have to be defined in the sensor's settings, not in the channel settings) and also demonstrates the usage of a different channel selection value method:

More

Note: There is a bug in PRTG 16.3.25 that prevents the sensor's message (from the SQL result) from being displayed correctly (it displays "Ok" instead). Update to the latest version if you are experiencing this issue.

Created on Aug 1, 2016 11:27:29 AM by Luciano Lingnau [Paessler]

Last change on Jan 4, 2023 2:40:07 PM by Brandy Greger [Paessler Support]



Votes:

1

I went through some pain trying to get this sensor setup in a way that would run an IBMi - DB2 SQL query that would do 2 things...

  1. Show in error status when more than 0 records were detected (count) for longer than 5 minutes... such as an activejob with LCKW status in a system table
  2. Display string values from the table that was being queried. e.g. the Job Number, Users Job, Job Name and status.

It turns out that this was not possible but came up with the next best thing:

I now run an SQL count query that triggers a custom vbscript ( Notification ) when the threshold is met.
The vbscript is the part that compiles the Email but is triggered at the right time by PRTG.
Here is my examples that need to go in C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\adosql as .sql files:

SQL for LCKW status to be added into sensor

SELECT SUBSYSTEM,SUBSTR(JOB_NAME,1,6) AS JOB_NUMBER,                       
SUBSTR(JOB_NAME,8,POSSTR(SUBSTR(JOB_NAME,8), '/')-1) AS JOB_USER,
SUBSTR(SUBSTR(JOB_NAME,8),POSSTR(SUBSTR(JOB_NAME,8),'/')+1) AS   
JOB_NAME, JOB_STATUS                                             
FROM TABLE (QSYS2.ACTIVE_JOB_INFO('NO','','','')) AS X           
where JOB_STATUS = 'LCKW' order by SUBSYSTEM

SQL for MSQG status to be added into sensor

SELECT SUBSYSTEM,SUBSTR(JOB_NAME,1,6) AS JOB_NUMBER,                       
SUBSTR(JOB_NAME,8,POSSTR(SUBSTR(JOB_NAME,8), '/')-1) AS JOB_USER,
SUBSTR(SUBSTR(JOB_NAME,8),POSSTR(SUBSTR(JOB_NAME,8),'/')+1) AS   
JOB_NAME, JOB_STATUS                                             
FROM TABLE (QSYS2.ACTIVE_JOB_INFO('NO','','','')) AS X           
where JOB_STATUS = 'MSGW' order by SUBSYSTEM

NOTE - These queries are for our specific version of IBMi v7.1 Now for the vbscript... note that this uses an open source postie.exe file to generate the Email and needs to be in the same script location here C:\Program Files (x86)\PRTG Network Monitor\Notifications\EXE:

On Error Resume Next
Dim strWkDir
strWkDir 				= Left(WScript.ScriptFullName,InStrRev(WScript.ScriptFullName,"\")-1)

Dim fso
Set fso = WScript.CreateObject("Scripting.Filesystemobject")
Dim WshShell
Set WshShell = WScript.CreateObject("Wscript.Shell")
Dim WshNetwork
Set WshNetwork = WScript.CreateObject("WScript.Network")
Dim strSQLReturn, sqlstr, strSendSMTP

Const ConnectString = "Provider=IBMDA400;Data Source=10.10.10.10;User Id=UserID;Password=UserIDPassword;"

Call IBMiSystemWaits("LCKW")
Call IBMiSystemWaits("MSGW")

If strSQLReturn = "" Then
	WScript.Quit
Else
End If

strSMTPSub 		= "IBMi LCKW or MSGW Detected"
strSMTPMes 		= "Please see the LCKW or MSGW details below..." & vbCrLf & vbCrLf & strSQLReturn & vbCrLf & vbCrLf & "Script run from " & UCase(WshNetwork.ComputerName) & ": " & WScript.ScriptFullName
strSMTPTo 		= "[email protected]"
strSendSMTP 	= Chr(34) & strWkDir & "\postie.exe" & Chr(34) & " -host:SMTPHOST.EmailSomeone.com -to:" & strSMTPTo & " -from:[email protected] -s:""" & strSMTPSub & """ -msg:""" & strSMTPMes & """"

WshShell.Run(strSendSMTP),0,False


Sub IBMiSystemWaits(strWaitType)

Set IBMics = CreateObject("ADODB.Connection")
Set IBMirs = CreateObject("ADODB.Recordset")

sqlstr = "SELECT SUBSYSTEM,SUBSTR(JOB_NAME,1,6) AS JOB_NUMBER,SUBSTR(JOB_NAME,8,POSSTR(SUBSTR(JOB_NAME,8), '/')-1) AS JOB_USER,SUBSTR(SUBSTR(JOB_NAME,8),POSSTR(SUBSTR(JOB_NAME,8),'/')+1) AS JOB_NAME, JOB_STATUS " & _
"FROM TABLE (QSYS2.ACTIVE_JOB_INFO('NO','','','')) AS X " & _
"where JOB_STATUS = '" & strWaitType & "' order by SUBSYSTEM"

IBMics.Open (ConnectString)
IBMirs.Open sqlstr, IBMics

With IBMirs
    If Not .BOF And Not .EOF Then
     
        While (Not .EOF)
            strSQLReturn = strSQLReturn & "SUBSYSTEM=" & Trim(IBMirs.Fields("SUBSYSTEM")) & _
            ", JOB_NUMBER=" & Trim(IBMirs.Fields("JOB_NUMBER")) & _
            ", JOB_USER=" & Trim(IBMirs.Fields("JOB_USER")) & _
            ", JOB_NAME=" & Trim(IBMirs.Fields("JOB_NAME")) & _
            ", JOB_STATUS=" & Trim(IBMirs.Fields("JOB_STATUS")) & vbcrlf
        .MoveNext
        Wend
    End If
    .Close
    
End With
End Sub

Hope this helps :-)

Created on Apr 20, 2018 4:15:41 PM

Last change on Apr 23, 2018 6:33:16 AM by Luciano Lingnau [Paessler]



Votes:

0

For system i (AS400), just use a query and a proper lookup file to map the status (UP / DOWN, ie. job in MSGW? ->1 = down). You don't need to introduce a script and maintain it, sending notifications via email is PRTG platform job.

See: https://kb.paessler.com/en/topic/63259-how-can-i-monitor-strings-from-an-sql-database-and-show-a-sensor-status-depending-on-it

Created on Jun 8, 2018 2:08:39 PM



Votes:

0

I'd also offer an extra bit of advice when using this sensor. (maybe it's common knowledge).

If using remote probes you will need to copy the SQL script you are using to the appropriate folder within the PRTG file structure on each of the remote probes. If you don't, any servers in that network segment, won't be able to select the script when setting up the SQL sensor.

Created on Jul 3, 2019 10:19:36 AM



Votes:

0

good job.

Created on Jan 14, 2022 1:25:09 PM




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.