PRTG Manual: ADO SQL v2 Sensor

Performance impact

This sensor has a high performance impact. We recommend that you use no more than 200 of this sensor on each probe.

SQL query storage

This sensor requires that you store the SQL query in a file on the probe system. In a cluster, copy the file to every cluster node.

For more information, see the Knowledge Base: Why do I have to store SQL sensor queries and custom scripts in files on the probe computer?

.NET 4.7.2 or later

This sensor requires .NET 4.7.2 or later from Microsoft on the probe system. In a cluster, install it on every cluster node.

If the framework is missing, you cannot create this sensor.

For more information, see the Knowledge Base: Which .NET version does PRTG require?

ODBC connection

If you use an ODBC connection, define the ODBC connection in the Windows ODBC Connection Manager first. If it is a 64-bit Windows, define the ODBC connection as an ODBC 32-bit connection.IPv6

Lookups

This sensor can use lookups. Select Lookup as Channel #x Unit and define the lookup file in Channel #x Lookup.

Credentials

Define the credentials for database management systems in settings that are higher in the object hierarchy.

Knowledge Base

• Knowledge Base: How to set up the SQL v2 sensors in PRTG? Is there a guide?
• Knowledge Base: How can I monitor strings from an SQL database and show a sensor status depending on it?
• Knowledge Base: How can I monitor error tables in SQL databases?
• Knowledge Base: Why do I have to store SQL sensor queries and custom scripts in files on the probe computer?

Hosted probe

You cannot add this sensor to the hosted probe of a PRTG Hosted Monitor instance. If you want to use this sensor, add it to a remote probe device.

IPv6

This sensor supports IPv6.

Connection String

Enter the connection string that this sensor uses to connect to the database. A connection string can look like this, for example:

Provider=SQLOLEDB.1;Data Source=10.0.0.200\SQLEXPRESS;User ID=user;Password=userpass;Initial Catalog=Northwind

For ODBC connections, you must enter MSDASQL as provider, for example Provider=MSDASQL;DSN=_my_odbc_sqlserver.

You can use the placeholders %dbloginuser and %dbloginpassword. PRTG replaces them with the credentials for database management systems of the parent device.

SQL Query File

Select the SQL query file that includes a valid SQL statement that the sensor executes on the server with every scanning interval. The list contains SQL scripts from the \Custom Sensors\sql subfolder of the PRTG program directory on the probe system. Store your script there. If you use the script on a cluster probe, you must store the script on all cluster nodes.

A correct expression in the file could be: SELECT AVG(UnitPrice) FROM Products. If you want to use transactions, separate the individual steps with semicolons ";".

Note that with each request, PRTG transfers the full result set, so use filters and limits in your query.

The demo script Demo Serveruptime.sql is available by default. You can use it to monitor the uptime of the target server.

See also the Knowledge Base: Why do I have to store SQL sensor queries and custom scripts in files on the probe computer?

You cannot change this value after sensor creation.

Input Parameter Handling

Define if you want to pass a parameter to the SQL query file:

• Do not use imput parameter (default): Execute the SQL query file without using variables.
• Use input parameter: Execute an SQL query file that contains a variable. Provide the parameter that you want to use in the query below.

Input Parameter

This setting is only visible if you select Use input parameter above.

Enter the parameter that you want to pass to the SQL query file. This parameter replaces the variables @prtg, :prtg, or ? in the SQL query, considering the general rules for SQL variables.

You can also use PRTG placeholders for custom sensors (command-line parameters) as input parameters, for example, %sensorid or %deviceid. For more information, see section Custom Sensors.

Provide strings as they are and do not surround them with quotation marks. PRTG automatically and correctly inserts string parameters into the query.

Transaction Handling

Define if you want to use transactions and if they affect the database content:

• Do not use transaction (default): Do not execute transactions.
• Use transaction and always roll back: The query does not change data in the database. In the SQL query file, separate the single steps of the transaction with semicolons.
• Use transaction and commit on success: The query changes data in the database. The changes only apply if all execution steps succeed without any errors. In the SQL query file, separate the single steps of the transaction with semicolons.

Data Processing

Define whether the sensor processes data from the database:

• Only execute query (default): Only show information about the number of affected rows and the execution time of the query. Affected rows are rows that were changed by the query (for example, created, deleted, or edited).
• Count table rows: Execute a SELECT statement and monitor how many rows of the data table this statement returns.
• Process data table: Read and analyze the data table. If you select this option, the sensor counts rows with SELECT statements as well.

You cannot change this value after sensor creation.

DBNull Handling

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation.

Define the sensor behavior if the query returns DBNull:

• Error: Show the Down status if the query returns DBNull.
• Number 0: Recognize the result DBNull as a valid value and interpret it as the number 0.

Select Channel Value by

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation.

Define how to select the desired cell in the database table:

• Column number: Determine the channel value by using the value in row 0 of the column whose number you specify in Channel #x Column Number.
• Column name: Determine the channel value by using the value in row 0 of the column whose name you specify in Channel #x Column Name.
• Row number: Determine the channel value by using the value in column 0 of the row whose number you specify in Channel #x Row Number.
• Key value pair: Determine the channel value by searching in column 0 for the key you specify in Channel #x Key and by returning the value in column 1 of the same row where the key value was found.

Defining how the desired cell in the database table is selected is necessary to configure the cells that are used in the channels.

The option you select here also defines the method of how to optionally determine a value for the sensor message. For more information, see setting Use Data Table Value in Message.

For an example for channel value selection, see section Monitoring Databases.

Channel #2 - #10

This setting is only visible if you select Process data table above.

You can define up to 10 channels. You must define at least one channel, so you see all available settings for Channel #1. Specify how to handle all other possible channels:

• Disable: Do not create this channel.
• Enable: Create this channel.

It is not possible to enable or disable channels after sensor creation.

Channel #x Name

This setting is only visible if you select Process data table above.

Enter a name for the channel. Enter a string. The sensor dynamically generates channels with this name as identifier.

If the name contains angle brackets (<>), PRTG replaces them with braces ({}) for security reasons. For more information, see the Knowledge Base: What security features does PRTG include?

Channel #x Column Number

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Column number for the setting Select Channel Value by.

Provide the number of the column to use to determine the channel value in row 0. Enter an integer.

Channel #x Column Name

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Column name for the setting Select Channel Value by.

Provide the name of the column to use to determine the channel value in row 0. Enter an integer.

Channel #x Row Number

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Row number for the setting Select Channel Value by.

Provide the number of the column to use to determine the channel value in row 0. Enter an integer.

Channel #x Key

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Key value pair for the setting Select Channel Value by.

Provide the key to search for in column 0 of the data table. The value in column 1 of the same row where the key value was found to use to determine the channel value. Enter a string.

Channel #x Mode

This setting is only visible if you select Process data table above.

Define how to display the determined value in the channel:

• Absolute (default): Show the value as the sensor retrieves it from the data table.
• Difference: The sensor calculates and shows the difference between the last and the current value returned from the data table.
  This mode is not compatible with the unit Lookup.
   
  This mode only works if the difference between the last and the current value is positive and increases with each scanning interval. This mode does not support negative and decreasing values.

• Absolute (recommended): Show the value as the sensor retrieves it from the data table.
• Difference: The sensor calculates and shows the difference between the last and the current value returned from the data table.
  This mode is not compatible with the unit Lookup.
   
  This mode only works if the difference between the last and the current value is positive and increases with each scanning interval. This mode does not support negative and decreasing values.

You cannot change this value after sensor creation.

Channel #x Unit

This setting is only visible if you select Process data table above.

Define the unit of the channel value:

• BytesBandwidth
• BytesMemory
• BytesDisk
• Temperature
• Percent
• TimeResponse
• TimeSeconds
• TimeHours
• Count
• CPU
• BytesFile
• SpeedDisk
• SpeedNet
• Custom
• Lookup

For more information about the available units, see section Custom Sensors.

To use lookups with this channel, select Lookup and define the lookup file in Channel #x Lookup. Do not use Custom if you use lookups with this sensor.

It is not possible to use the unit Lookup in combination with the Difference mode. You are not able to create the sensor in this case.

Channel #x Custom Unit

This setting is only visible if you select the channel unit Custom above.

Define a unit for the channel value. Enter a string.

Channel #x Lookup

This setting is only visible if you select the channel unit Lookup above.

Select a lookup file that you want to use with this channel.

Use Data Table Value in Message

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation.

Define if the sensor message shows a value from the data table:

• Disable: Do not use a custom sensor message.
• Enable: Define a custom sensor message with a defined value of the data table. Define the value selection below.

The method of how to determine a value for the sensor message is defined in the setting Select Channel Value by above.

Message Column Number

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Column name for the setting Select Channel Value by, and if you select Enable above.

Enter the number of a column. The sensor message shows the value in row 0 of this column. Enter an integer.

Message Column Name

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Column name for the setting Select Channel Value by, and if you select Enable above.

Enter the name of a column. The sensor message shows the value in row 0 of this column. Enter a string.

Columns start with index 0.

Message Row Number

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Row number for the setting Select Channel Value by, and if you select Enable above.

Enter the name of a column. The sensor message shows the value in row 0 of this column. Enter the number of a row. The sensor message shows the value in column 0 of this row. Enter an integer.

Rows start with index 0.

Message Key

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Key value pair for the setting Select Channel Value by, and if you select Enable above.

Enter a key to search for in column 0 of the data table. The sensor message shows the value in column 1 of the row where the key was found. Enter a string.

Message

This setting is only visible if you select Enable above.

Define the sensor message. Enter a string. Use the placeholder {0} at the position where you want to display the value.

Example: The message is {0}

PRTG does not support the number sign (#) in sensor messages. If a message contains a number sign, PRTG clips the message at this point.

If Message Changes

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation.

Define what the sensor does when its message changes:

• Ignore (default): Do nothing.
• Trigger 'change' notification: Send an internal message that indicates a change.
  In combination with a change trigger, you can use this to trigger a notification if a change occurs.

Result Handling

Define what PRTG does with the sensor result:

• Discard result (default): Do not store the sensor result.
• Store result: Store the sensor result and the last response in the \Logs\sensors subfolder of the PRTG data directory on the probe system. The file names are Result of Sensor [ID].txt, Result of Sensor [ID].Data.txt, and Result of Sensor [ID].log. This setting is for debugging purposes. PRTG overwrites these files with each scanning interval.

In a cluster, PRTG stores the result in the PRTG data directory of the master node.

Primary Channel

Select a channel from the list to define it as the primary channel. In the device tree, PRTG displays the last value of the primary channel below the sensor's name. The available options depend on what channels are available for this sensor.

You can set a different primary channel later by clicking below a channel gauge on the sensor's Overview tab.

Graph Type

Define how this sensor shows different channels:

• Show channels independently (default): Show a graph for each channel.
• Stack channels on top of each other: Stack channels on top of each other to create a multi-channel graph. This generates a graph that visualizes the different components of your total traffic.
  You cannot use this option in combination with manual Vertical Axis Scaling (available in the channel settings).

Stack Unit

This setting is only visible if you select Stack channels on top of each other above.

Select a unit from the list. PRTG stacks all channels with this unit on top of each other. By default, you cannot exclude single channels from stacking if they use the selected unit. However, there is an advanced procedure to do so.

Affected Rows

The number of rows that were addressed by the query (including SELECT statements if you process data tables)

Downtime

In the channel table on the Overview tab, this channel never shows any values. PRTG uses this channel in graphs and reports to show the amount of time in which the sensor was in the Down status.

Execution Time

The execution time of the entire request (including connection buildup, query execution, transaction handling, disconnection)

This channel is the primary channel by default.

Query Execution Time

The execution time of the specified query