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 can I monitor error tables in SQL databases?

Votes:

0

I would like to monitor an error table in my SQL database with PRTG. If there is an error entry in this data table, PRTG should display this error. Otherwise, the respective SQL sensor should remain in the Up status to show me that everything is OK.

Can I monitor database tables that record errors with SQL v2 sensors? How can the SQL query look and what do I have to consider regarding the SQL sensor configuration?

database database-error database-health prtg sql sql-query sql-v2

Created on Aug 11, 2016 12:34:31 PM by  Gerald Schoch [Paessler Support]

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



1 Reply

Accepted Answer

Votes:

0

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

Created on Aug 11, 2016 12:41:27 PM by  Gerald Schoch [Paessler Support]

Last change on Dec 29, 2022 12:25:17 PM by  Brandy Greger [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.