This article applies as of PRTG 22

Monitoring error tables in SQL databases

You can check error tables in your SQL databases with one of the SQL v2 sensors and an according SQL script. For example, write a script that queries a data table where database errors are recorded. If there is content in this table, you know that there is a broken database. You can configure the sensor to change to the Down status in this case and show the specific error in the sensor message.

Use the following sample SQL query as a baseline for your own scenario:

select count(*) as Count, (select e.error + ', ' from error e FOR XML PATH('')) as Errors from error

This query reads the number of entries from a data table dbo.error. It also retrieves the column error from this table and concatenates all error messages separated by a comma. If there is no error, the column returns NULL, so select the option Number 0 in the sensor settings.

You can use the value from the column Count for the first sensor channel and the column Errors for the sensor message. If there is no error, the sensor message is 0. Adjust the SQL query to your scenario and use it with an SQL v2 sensor.

Step by step

1. Open a text editor and adjust the sample SQL from above according to your needs (for example, provide the data table from which you want to read the errors or exclude acknowledged errors from the count).
2. Save the file with the SQL query in the PRTG program directory on the probe system with your SQL sensor under \Custom Sensors\sql\<%sql_flavor%>. This depends on the type of SQL sensor you use.
3. If you have not yet done so, add a device to PRTG that represents the target database. Define credentials for this database in the Credentials for Database Management Systems section of the device settings.
4. Add an SQL sensor to this device. The type depends on the database: Microsoft SQL, MySQL, Oracle SQL, PostgreSQL, or ADO SQL.
5. Configure the settings of the respective sensor as follows. You can leave settings that are not mentioned here unchanged.
   1. Select the SQL Query File you have created before.
   2. Select the option Process Data Table for the Data Processing setting.
   3. For the setting Handle DBNull in Channel Values as, select the option Number 0.
   4. Select the option Column name for the Select Channel Value by setting.
   5. Enter a Sensor Channel #1 Name, for example, “Error count”.
   6. Enter Count in the Sensor Channel #1 Column Name field.
   7. The Sensor Channel #1 Mode is Absolute and the Sensor Channel #1 Unit is Count
   8. Enable the setting Use Data Table Value in Sensor Message.
   9. Enter Errors in the Sensor Message Column Name field.
   10. Adjust the Sensor Message to your needs. To only display errors, just use {0} as placeholder.
       Note: If there are no errors, the sensor message displays 0.
6. Click Create to add the sensor.
7. To show the Down status if the sensor detects database errors, open the settings of the Error count channel, enable Limits, and enter 0 in the Upper Error Limit field.

More

• How to set up the SQL v2 sensors in PRTG? Is there a guide?
• How can I monitor strings from an SQL database and show a sensor status depending on it?