PRTG Manual: Microsoft SQL v2 Sensor
The Microsoft SQL v2 sensor monitors a database on a Microsoft SQL server and executes a query.
The sensor can also process the data table and show the values that you define in individual channels.
For a detailed list and descriptions of the channels that this sensor can show, see section Channel List.
- Dutch: Microsoft SQL v2
- French: Microsoft SQL v2
- German: Microsoft SQL v2
- Japanese: Microsoft SQL v2
- Portuguese: Microsoft SQL v2
- Russian: Microsoft SQL v2
- Simplified Chinese: Microsoft SQL v2
- Spanish: Microsoft SQL v2
- This sensor has a high performance impact. Use it with care. We recommend that you use no more than 200 sensors of this sensor type on each probe.
- You must store your Structured Query Language (SQL) query in a file on the probe system. In a cluster, copy the file to every cluster node.
- This sensor requires Microsoft .NET 4.7.2 or later on the probe system.
- Define credentials, custom port (if required), and timeout in the credentials for database management systems settings of the parent device, or in the settings of a group or probe above.
- This sensor supports Microsoft SQL server 2005 or later.
- This sensor supports the IPv6 protocol.
- See section Monitoring Databases for an example for channel value selection.
- See the Knowledge Base: How to set up the SQL v2 sensors in PRTG? Is there a guide?
- See the Knowledge Base: How can I monitor strings from an SQL database and show a sensor status depending on it?
- See the Knowledge Base: How can I monitor error tables in SQL databases?
- See the Knowledge Base: Why do I have to store SQL sensor queries and custom scripts in files on the probe computer?
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.
Requirement |
Description |
---|---|
.NET 4.7.2 or later |
This sensor requires .NET 4.7.2 or later to be installed on the probe system (on every cluster node, if on a cluster probe).
|
The Add Sensor dialog appears when you manually add a new sensor to a device. It only shows the settings that are required to create the sensor. You can change nearly all settings on the sensor's Settings tab after creation.
Data
Setting |
Description |
---|---|
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 ";".
|
Data Processing |
Define whether the sensor processes data from the database:
|
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 data channel, so you see all available settings for Channel #1. Specify how to handle all other possible channels:
|
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.
|
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:
|
Channel #x Unit |
This setting is only visible if you select Process data table above. Define the unit of the channel value:
|
Click the Settings tab of a sensor to change its settings.
Setting |
Description |
---|---|
Sensor Name |
Enter a name to identify the sensor. |
Parent Tags |
Shows tags that the sensor inherits from its parent device, parent group, and parent probe.
|
Tags |
Enter one or more tags. Confirm each tag with the Spacebar key, a comma, or the Enter key. You can use tags to group objects and use tag-filtered views later on. Tags are not case-sensitive. Tags are automatically inherited.
The sensor has the following default tags that are automatically predefined in the sensor's settings when you add the sensor:
|
Priority |
Select a priority for the sensor. This setting determines the position of the sensor in lists. The highest priority is at the top of a list. Choose from the lowest priority ( |
Setting |
Description |
---|---|
Database |
Enter the name of the SQL database to which the sensor connects, for example, MyDatabase. This is a logical entity on the database server where database objects exist. |
SQL Server Instance |
Define if you want to use an instance name for the database connection:
|
Instance Name |
This setting is only visible if you select Use instance name above. Enter the name of the instance that you want to monitor. |
Encryption |
Define the encryption usage for the database connection:
|
Setting |
Description |
---|---|
SQL Query File |
Shows the SQL script file that the sensor executes on the server.
|
Input Parameter Handling |
Define if you want to pass a parameter to the SQL query file:
|
Input Parameter |
This setting is only visible if you select Input Parameter Handling 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 details, see section Custom Sensors.
|
Transaction Handling |
Define if you want to use transactions and if they affect the database content:
|
Data Processing |
Shows how the sensor processes data from the database.
|
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:
|
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:
|
Channel #x |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. The setting shows if the channel is disabled.
|
Channel #x Name |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Enter a name for the channel. Enter a string. Enter a name for the channel. Enter a string. The sensor dynamically generates channels with this name as identifier.
|
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 value. |
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 value. |
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 value. |
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 for the setting Data Processing during sensor creation. The setting shows how you want to display the determined value in the channel.
|
Channel #x Unit |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Define the unit of the channel value:
|
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 Sensor 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:
The method of how to determine a value for the sensor message is defined in the setting Select Channel Value by above. |
Sensor 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 number for the setting Select Channel Value by, and if you enable Use Data Table Value in Sensor Message. Enter the number of a column. The sensor message shows the value in row 0 of this column. Enter an integer value. |
Sensor 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 enable Use Data Table Value in Sensor Message. Enter the name of a column. The sensor message shows the value in row 0 of this column. Enter a string.
|
Sensor 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 enable Use Data Table Value in Sensor Message. 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 value.
|
Sensor 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 enable Use Data Table Value in Sensor Message. 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. |
Sensor Message |
This setting is only visible if you enable Use Data Table Value in Sensor Message. 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}
|
If Sensor 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 the sensor message changes:
|
Result Handling |
Define what PRTG does with the sensor result:
|
Setting |
Description |
---|---|
Primary Channel |
Select a channel from the list to define it as the primary channel. In the device tree, the last value of the primary channel is always displayed below the sensor's name. The available options depend on what channels are available for this sensor.
|
Graph Type |
Define how different channels are shown for this sensor:
|
Stack Unit |
This setting is only visible if you enable Stack channels on top of each other as Graph Type. Select a unit from the list. All channels with this unit are stacked 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. |
By default, all of the following settings are inherited from objects that are higher in the hierarchy. We recommend that you change them centrally in the root group settings if necessary. To change a setting for this object only, click under the corresponding setting name to disable the inheritance and to display its options.
For more information, see section Inheritance of Settings.
Click to interrupt the inheritance.
Setting |
Description |
---|---|
Scanning Interval |
Select a scanning interval from the dropdown list. The scanning interval determines the amount of time that the sensor waits between two scans. Choose from:
|
If a Sensor Query Fails |
Select the number of scanning intervals that the sensor has time to reach and to check a device again if a sensor query fails. Depending on the option that you select, the sensor can try to reach and to check a device again several times before the sensor shows the Down status. This can avoid false alarms if the monitored device only has temporary issues. For previous scanning intervals with failed requests, the sensor shows the Warning status. Choose from:
|
Schedules, Dependencies, and Maintenance Window
You cannot interrupt the inheritance for schedules, dependencies, and maintenance windows. The corresponding settings from the parent objects are always active. However, you can define additional schedules, dependencies, and maintenance windows. They are active at the same time as the parent objects' settings.
Setting |
Description |
---|---|
Schedule |
Select a schedule from the list. You can use schedules to monitor during a certain time span (days or hours) every week. Choose from:
|
Maintenance Window |
Select if you want to set up a one-time maintenance window. During a maintenance window, monitoring stops for the selected object and all child objects. They show the Paused status instead. Choose between:
|
Maintenance Begins |
This setting is only visible if you enable Set up a one-time maintenance window above. Use the date time picker to enter the start date and time of the one-time maintenance window. |
Maintenance Ends |
This setting is only visible if you enable Set up a one-time maintenance window above. Use the date time picker to enter the end date and time of the one-time maintenance window. |
Dependency Type |
Select a dependency type. You can use dependencies to pause monitoring for an object depending on the status of a different object. You can choose from:
|
Dependency |
This setting is only visible if you enable Select a sensor above. Click |
Dependency Delay (Sec.) |
This setting is only visible if you select Select a sensor above. Define a time span in seconds for the dependency delay. After the master sensor for this dependency returns to the Up status, PRTG additionally delays the monitoring of the dependent objects by the time span you define. This can prevent false alarms, for example, after a server restart or to give systems more time for all services to start. Enter an integer value.
|
Click to interrupt the inheritance.
Setting |
Description |
---|---|
User Group Access |
Define the user groups that have access to the sensor. You see a table with user groups and group access rights. The table contains all user groups in your setup. For each user group, you can choose from the following group access rights:
|
Click to interrupt the inheritance.
Which channel units are available depends on the sensor type and the available parameters. If no configurable channels are available, this field shows No configurable channels.
Setting |
Description |
---|---|
Channel Unit Types |
For each type of channel, select the unit in which PRTG displays the data. If you define this setting on probe, group, or device level, you can inherit these settings to all sensors underneath. You can set units for the following channel types (if available):
|
Which channels the sensor actually shows might depend on the monitored device, the available components, and the sensor setup.
Channel |
Description |
---|---|
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 in percent. |
Execution Time |
The execution time of the entire request (including connection buildup, query execution, transaction handling, disconnection) in milliseconds (msec)
|
Execution Time |
The execution time of the specified query in msec |
Knowledge Base
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?
How can I monitor error tables in SQL databases?
Why do I have to store SQL sensor queries and custom scripts in files on the probe computer?
Which .NET version does PRTG require?
What security features does PRTG include?
How do I monitor the size of a Microsoft SQL Server database?
For more information about sensor settings, see the following sections: