New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


How can I monitor error tables in SQL databases?

Votes:

0

Your Vote:

Up

Down

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 according SQL sensor should remain in up status to show me that everything is OK.

Can I monitor database tables recording errors with SQL v2 sensors? How can the SQL query look like and what do I have to consider for 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 Aug 11, 2016 3:51:19 PM by  Gerald Schoch [Paessler Support]



1 Reply

Accepted Answer

Votes:

0

Your Vote:

Up

Down

This article applies to PRTG Network Monitor 16 or later

Monitoring Error Tables in SQL Databases

You can check error tables in your SQL databases with one of the SQL v2 sensor types 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 a 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 comma. If there is no error, the column returns NULL, so choose the option Number 0 in the DBNull settings of the sensor.

You can use the value from column Count for the first sensor channel and the column Errors for the sensor message. If there is no error, the sensor message will be 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 where you want to read the errors or exclude acknowledged errors from the count).
  2. Save the file with the SQL query in your PRTG installation directory on the computer running the probe with your SQL sensor under \Custom Sensors\sql\<%sql_flavor%>, depending on the type of SQL sensor you use.
  3. If not done yet, 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 sensor as follows. You can leave settings that are not mentioned here unchanged.
    1. Choose the SQL Query File you have created before.
    2. Choose the option Process Data Table for the Data Processing setting.
    3. For the setting Handle DBNull in Channel Values as choose the option Number 0.
    4. Choose 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 into the Sensor Channel #1 Column Name field.
    7. Mode is Absolute and Unit is Count
    8. Enable the setting Use Data Table Value in Sensor Message.
    9. Enter Errors into the Sensor Message Column Name field.
    10. Adjust the Sensor Message to your needs. To only display the errors, just use {0} as placeholder.
      Note: If there are no errors, the sensor message will display 0.
  6. Click Continue to add the sensor.
  7. To show a down status if the sensor detects database errors, open the settings of the Error count channel, Enable Limits, and enter 0 into the Upper Error Limit field.

See Also

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

Last change on Aug 11, 2016 4:09:48 PM by  Gerald Schoch [Paessler Support]



Please log in or register to enter your reply.


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.