I have a few SQL servers in my environment and would like to monitor the results of specific queries using PRTG. Can I use this for alerting and notifications? What set-up steps are required?
This article applies to PRTG Network Monitor 16 or later
Configuration of SQL v2 Sensors
There are a couple of SQL sensor types available in PRTG. Each sensor type is compatible with a different SQL "flavor":
The setup for all SQL sensors is roughly the same.
In this example we'll 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 the 3.3 Polishing section which explains how to define limits. This replaces the previous "Post-Processing warning and error conditions" setting from the SQL v1 sensors.
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 (mostly) any SQL query, but you need to code the query yourself. You can do a lot of cool stuff and create valuable dashboards (for non-IT departments of your organization).
- How much did my company sell today?
- What's the average order price?
- How many customers logins are active, logged in, exist?
The possibilities are infinite, as long as you're 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 equivalent management tool of your SQL server and focus solely 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 (values) returned by the sensor should not be more than 10 and the amount of results should be static (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'll work with this simple SQL query:
SELECT count(idorder), sum(value), avg(value), GROUP_CONCAT(customer SEPARATOR ', ') from test.order
2. Storing the Script
If you have created your script with the SQL query and it runs and works in the SQL Editor, it's 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:
|C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\%yoursqlflavor%\|
In this example we're working with MySQL, so our script has to be stored in the mysql folder. We name it Custom Example.sql. 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 write the query directly within the sensor's settings. For security reasons (because it would allow someone to modify the query directly from within the web interface), we now require the script to be 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. See this article for details.
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.
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. Please see the sensor's remarks section in the manual.
3.1 Defining the Credentials for Database Sensors
With the SQL v2 sensors we require credentials to be 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
On the device where you want to deploy the sensor(s), click Add Sensor and search for SQL, select the appropriate sensor version that matches your SQL flavor.
Now within 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.
Under the Data section, select the SQL Query File (the file you've saved in Step 1), define the Transaction approach, and if you want PRTG to read/evaluate the results of the query, 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.
Now it's time to decide on the channel selection approach. The standard (and simplest) option is the default Column number approach, which means that the multiple results from the query will be assigned with incremental numbers, 0, 1, 2, 3 and so-on. Our example script returns 4 rows, we use Column number as Select Channel Value by.
As previously tested/defined, the example script returns the following information from the "order" table.
- The count of unique orders
- The sum of the value from all orders
- The avg value from all orders
- A string which contains the customer names (interesting if you want to identify results in a TOP kind query)
To assign the results to channels, enable the desired number of channels (by default there's only one channel enabled), specify the channels name (which do not have to match the field's name), and enter (because we're working with select channel by column number) the column number that you want to assign to that channel. The first column has the ID 0 (zero). Please review the "filled-out" setup in the screenshot below:
You're not required to 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 (e.g. 1 = OK, 2 = Broken, 3 = Success) you need to employ lookups. These should be defined during the sensor's creation (and not via the channel settings later). For further details about lookups please check:
3.3 Polishing (Primary channel, limits, etc)
The previous steps are sufficient to get a working sensor, now there are steps which you can take to "polish" the created sensor.
- Use the "pin" to define your primary channel. The primary channel is somewhat special, its value is displayed in notifications and as sensor value when browsing the device tree.
- Click a channel to open up its settings and use this window to define limits which are very useful for alerting (set the sensor into warning or down if the value of the channel goes above or below a specific threshold).
By the end your sensor should look like this:
The sensor's debugging (if you're encountering issues) is possible via the Sensor Result: Write sensor result to disk (Filename: "Result of Sensor [ID].txt") option from within the sensor's settings. When this option is set, the following log files will be stored to disk on every sensor scan (on the probe were you created the sensor) and will provide debugging information about the sensor's execution.
|Default path (on the probe system)||C:\ProgramData\Paessler\PRTG Network Monitor\Logs (Sensors)|
Once you're done with the debugging we advise you to disable the Save results to disk option again as it can affect the sensor's performance.
|Result of Sensor [ID].Data.txt||Contains a collection of the sensor's parameters/settings|
|Result of Sensor [ID].log||Will debug/log the connection to the database, settings, timings, and the received replies. Useful for debugging.|
|Result of Sensor [ID].txt||The actual output passed to PRTG, will usually contain the very same error/issue message and values as you see in the PRTG web interface (sensor message)|
If you're encountering issues and the debug files don't tell you anything about the error you should still keep them if you decide to contact PRTG's tech support as they will assist us with the troubleshooting.
5. Related Topics
The example below explains the usage of lookups (which 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:
- How can I monitor error tables in SQL databases?
- Microsoft SQL v2 in Difference mode
- SQL Sensor to Return Positive Result if No Return?
- Could not convert variant of type (UnicodeString) into type (Double)
NOTE: There's a bug in PRTG 16.3.25 which prevents the "Sensor's message" (from the SQL result) from being displayed correctly(It displays "Ok" instead). Please update to the latest version if you're experiencing this issue.
Luciano Lingnau [Paessler Support]
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...
- 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
- 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 = "EmailAddress@EmailSomeone.com" strSendSMTP = Chr(34) & strWkDir & "\postie.exe" & Chr(34) & " -host:SMTPHOST.EmailSomeone.com -to:" & strSMTPTo & " -from:PRTG_Alerts@EmailSomeone.com -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 :-)
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.