<%NUMBERING1%>.<%NUMBERING2%>.<%NUMBERING3%> PRTG Manual: Microsoft SQL v2 Sensor

The Microsoft SQL v2 sensor monitors a database on a Microsoft SQL server and executes a defined query.

The sensor can show the following:

  • Execution time of the whole request (including connection buildup, query execution, transaction handling, disconnection)
  • Execution time of the defined query
  • Number of rows that were addressed by the query (including SELECT statements if you process data tables)
  • It can also process the data table and show defined values in individual channels.
Microsoft SQL v2 Sensor

Microsoft SQL v2 Sensor

Remarks

  • You must store your Structured Query Language (SQL) query in a file on the system running the probe where you create the sensor. If you use the sensor on a remote probe, store the file on the system running the remote probe. In a cluster setup, copy the file to every cluster node.
  • Requires .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.

icon-prtg-on-demandYou cannot add this sensor to the Hosted Probe of a PRTG hosted by Paessler instance. If you want to use this sensor, add it to a remote probe device.

Requirement: .NET Framework

icon-toolsThis sensor requires the Microsoft .NET Framework. .NET 4.7.2 or later must be installed on the computer running the PRTG probe, either on the local system (on every node, if on a cluster probe), or on the system running the remote probe. If the framework is missing, you cannot create this sensor.

icon-book-bulbFor more information, see the Knowledge Base: Which .NET version does PRTG require?

Add Sensor

The Add Sensor dialog appears when you manually add a new sensor to a device. It only shows the setting fields that are required for creating the sensor. Therefore, you will not see all setting fields in this dialog. You can change (nearly) all settings in the sensor's Settings tab later.

Sensor Settings

On the details page of a sensor, click the Settings tab to change its settings.

icon-i-roundUsually, a sensor connects to the IP Address or DNS Name of the parent device on which you created the sensor. See the Device Settings for details. For some sensors, you can explicitly define the monitoring target in the sensor settings. See below for details on available settings.

Basic Sensor Settings

Sensor Name

Enter a meaningful name to identify the sensor. By default, PRTG shows this name in the device tree, as well as in alarms, logs, notifications, reports, maps, libraries, and tickets.

Parent Tags

Shows Tags that this sensor inherits from its parent device, group, and probe. This setting is shown for your information only and cannot be changed here.

Tags

Enter one or more Tags, separated by spaces or commas. You can use tags to group sensors and use tag–filtered views later on. Tags are not case sensitive. We recommend that you use the default value.

There are default tags that are automatically predefined in a sensor's settings when you add a sensor. See section Default Tags below.

You can add additional tags to the sensor if you like. Other tags are automatically inherited from objects further up in the device tree. These are visible above as Parent Tags.

icon-i-roundIt is not possible to enter tags with a leading plus (+) or minus (-) sign, nor tags with parentheses (()) or angle brackets (<>).

Priority

Select a priority for the sensor. This setting determines where the sensor is placed in sensor lists. A sensor with a top priority is at the top of a list. Choose from one star (low priority) to five stars (top priority).

Default Tags

sqlsensor

Database Specific

Database

Enter the name of the SQL database to which the sensor connects. For example, such a database name could be MyDatabase. This is a logical entity on the database server where database objects like tables or stored procedures exist.

SQL Server Instance

Define if you want to use an instance name for the database connection. Choose between:

  • No instance name required (default): Use the default instance for the connection.
  • Use instance name: Use a named instance that you can specify below.

Instance Name

This field is only visible if you enable Use instance name above. Enter the named instance that you want to monitor.

Encryption

Define encryption usage for the database connection. Choose from:

  • Use server defaults (default): The database connection is only encrypted if enforced by the database server.
  • Enforce encryption but do not validate server certificate: Select this option to make sure that the database connection is encrypted.
  • Enforce encryption and validate server certificate: Select this option to force encryption and to validate the database server certificate. This approach provides highest security, for example, it helps to prevent man-in-the-middle attacks.
    icon-i-round-redThe sensor only validates the certificate if the database server enforces encryption.

Data

SQL Query File

Select a SQL script file that includes a valid SQL statement to be executed on the server. Once you have created a sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, please add the sensor anew.

The script will be executed with every scanning interval. The list contains SQL scripts from the database management system-specific subfolder \Custom Sensors\sql of your PRTG installation. Store your script there. If used on a remote probe, the file must be stored on the system running the remote probe. If used on a cluster probe, you must store the file on all servers running a cluster node.

icon-book-arrowsFor more information on how to find this path, see section Data Storage. By default, the demo script Demo Serveruptime.sql that you can use to monitor the uptime of the target server is available.

For example, 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 ";".

icon-i-roundNote that with each request, the full result set will be transferred, so use filters and limits in your query.

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

SQL Variables

You can use the following variables in your query file to be replaced by an input parameter. This is useful if you have various SQL sensors with queries that differ in only one parameter.

  • Microsoft SQL, MySQL, PostgreSQL: @prtg
  • Oracle SQL: :prtg
  • ADO SQL: ? (question mark)

icon-i-round@prtg, :prtg, and ? are common SQL query parameters and are used in a parameterized SQL query. This means that the query and the parameter are forwarded to the database without any changes. This leads to some restrictions on the database side. For example, variables cannot be used as placeholders for table names, or as lists in IN operators.

Examples for variables usage:

SELECT * FROM Table WHERE name = @prtg

SELECT @prtg FROM Table

Use Input Parameter

Define if you want to pass a parameter to your SQL query file. This will replace the variables @prtg (Microsoft SQL, MySQL, PostgreSQL), :prtg (Oracle SQL), or ? (ADO SQL) in the SQL query, considering the general rules for SQL variables. Choose between:

  • Don't use input 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 field is only visible if you enable Use input parameter above. Enter the parameter that you want to pass to the SQL query file. This parameter will replace 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.

icon-i-roundProvide strings as they are and do not surround them with quotation marks. PRTG will correctly insert string parameters into the query automatically.

Use Transaction

Define if you want to use transactions and if they will affect the database content. Choose from:

  • Don't use transaction (default): No transactions will be executed.
  • Use transaction and always rollback: Select this option to ensure that no data in the database will be changed by the query. In the SQL query file, separate the single steps of the transaction with semicolons.
  • Use transaction and commit on success: Select this option to perform changes to the database with the query. The changes will 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 if you want to process data from the database. Once you have created a sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, please add the sensor anew. Choose from:

  • Just execute the query: If you select this option, the sensor will only show information about the number of affected rows and the execution time of the query. Affected rows are rows that were changed somehow by the query (for example, created, deleted, or edited).
  • Count table rows: Select this option if you execute a SELECT statement and want to monitor how many rows of the data table this statement returns.
  • Process data table: Select this option to read and analyze the queried data table. If you select this option, the sensor will count rows with SELECT statements as well.

Handle DBNull in Channel Values as

This setting is only visible if you select Process data table above. Define the sensor behavior if DBNull is returned by the query. Choose between:

  • Error: The sensor will show a Down status if DBNull is reported.
  • Number 0: The sensor will 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 above. Define how the desired cell in the database table will be selected. This is necessary to configure the cells that will be used in the sensor channels. Choose from:

  • Column number: The channel value will be determined by using the value in row 0 of the column whose number you specify below.
  • Column name: The channel value will be determined by using the value in row 0 of the column whose name you specify below.
  • Row number: The channel value will be determined by using the value in column 0 of the row whose number you specify below.
  • Key value pair: The channel value will be determined by searching in column 0 for the key you specify below and returning the value in column 1 of the same row where the key value was found.

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

icon-book-arrowsSee section Monitoring Databases for an example for channel value selection.

Sensor Channel #x

This setting is only visible if you select Process data table above. You can define up to 10 different channels for the data processing of this sensor. You have to define at least one data channel if you process the data table, so you will see all available settings for Channel #1 without manually enabling it. For all other possible channels, choose between:

  • Disable: This channel will not be added to the sensor.
  • Enable: This channel will be added to the sensor. Define the settings as described above.

Once you have created a sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, please add the sensor anew.

Sensor Channel #x Name

This setting is only visible if you select Process data table above. Enter a unique name for the channel. Enter a string. Channels will be generated dynamically with this name as identifier. Once you have created a sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, please add the sensor anew.

Sensor Channel #x Column Number

This setting is only visible if you select Column number above. Provide the number of the column that will be used to determine the channel value in row 0. Enter an integer value.

Sensor Channel #x Column Name

This setting is only visible if you select Column number above. Provide the name of the column that will be used to determine the channel value in row 0. Enter a string.

Sensor Channel #x Row Number

This setting is only visible if you select Row number above. Provide the number of the row that will be used to determine the channel value in column 0. Enter an integer value.

Sensor Channel #x Key

This setting is only visible if you select Key value pair above. 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 will be used to determine the channel value. Enter a string.

Sensor 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. Once you have created a sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, please add the sensor anew. Choose between:

  • Absolute (recommended): Shows 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 Value Lookup.

Sensor Channel #x Unit

This setting is only visible if you select Process data table above. Define the unit of the channel value. Once you have created a sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, please add the sensor anew. Choose from:

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

icon-book-arrowsFor more information about the available units, see section Custom Sensors.

icon-i-roundTo use lookups with this channel, choose Value Lookup and select your lookup file below. Do not use Custom when using lookups with this sensor!

icon-i-blueUsing the unit Value Lookup is not possible when you choose the Difference mode. You will not be able to create the sensor in this case.

Sensor Channel #x Custom Unit

This setting is only visible if you select Custom above. Define a unit for the channel value. Enter a string.

Sensor Channel #x Value Lookup

This settings is only visible if you select Value 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 above. Define if the sensor message will show a value from the data table. Choose between:

  • 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.

Sensor Message Column Number

This setting is only visible if you select Column number and Use Data Table Value in Sensor Message above. Enter the number of a column. The sensor message will show the value in row 0 of this column. Enter an integer value.

icon-i-roundColumns and rows start with index 0.

Sensor Message Column Name

This setting is only visible if you select Column name and Use Data Table Value in Sensor Message above. Enter the name of a column. The sensor message will show the value in row 0 of this column. Enter a string.

icon-i-roundColumns and rows start with index 0.

Sensor Message Row Number

This setting is only visible if you select Row number and Use Data Table Value in Sensor Message above. Enter the number of a row. The sensor message will show value in column 0 of this row. Enter an integer value.

icon-i-roundColumns and rows start with index 0.

Sensor Message Key

This setting is only visible if you select Key value pair and Use Data Table Value in Sensor Message above. Enter a key to search for in column 0 of the data table. The sensor message will show the value in column 1 of the row where the key has been found. Enter a string.

icon-i-roundColumns and rows start with index 0.

Sensor Message

This setting is only visible if you select Use Data Table Value in Sensor Message above. Define the sensor message. Enter a string. Use the placeholder {0} at the position where the value will be added.

Example: The message is {0}

icon-i-blueThe number sign (#) is not supported in sensor messages. If a message contains a number sign, the message will be cut off at this point.

If Sensor Message Changes

Define what this sensor will do when the sensor value changes. Choose between:

  • Ignore changes (default): The sensor takes no action on change.
  • Trigger 'change' notification: The sensor sends an internal message indicating that its value has changed. In combination with a Change Trigger, you can use this mechanism to trigger a notification whenever the sensor value changes.

Sensor Result

Define what PRTG will do with the sensor results. Choose between:

  • Discard sensor result: Do not store the sensor result.
  • Write sensor result to disk (File name: Result of Sensor [ID].txt): Store the last result received from the sensor to the Logs (Sensors) subfolder of the PRTG data directory on the probe system the sensor is running on (on the Master node if in a cluster). File names: Result of Sensor [ID].txt and Result of Sensor [ID].Data.txt. This is for debugging purposes. PRTG overwrites these files with each scanning interval. icon-book-arrows
    For more information on how to find the folder used for storage, see section Data Storage.

icon-prtg-on-demandThis option is not available when the sensor runs on the Hosted Probe of a PRTG hosted by Paessler instance.

Sensor Display

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.

icon-i-roundYou can set a different primary channel later by clicking the pin symbol of a channel on the sensor's Overview tab.

Graph Type

Define how different channels will be shown for this sensor.

  • 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 will generate a graph that visualizes the different components of your total traffic.
    icon-i-roundThis option cannot be used in combination with manual Vertical Axis Scaling (available in the Sensor Channel Settings settings).

Stack Unit

This field is only visible if you enable Stack channels on top of each other above. Select a unit from the list. All channels with this unit will be 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.

Inherited Settings

By default, all of the following settings are inherited from objects that are higher in the hierarchy and should be changed there if necessary. Often, best practice is to change them centrally in the Root group's settings. For more information, see section Inheritance of Settings. To change a setting for this object only, disable inheritance by clicking the button next to inherit from under the corresponding setting name. You will then see the options described below.

Scanning Interval

Click inherited_settings_button to interrupt the inheritance. See section Inheritance of Settings for more information.

Scanning Interval

Select a scanning interval (seconds, minutes, or hours). The scanning interval determines the amount of time that the sensor waits between two scans. You can change the available intervals in the system administration on PRTG on premises installations.

If a Sensor Query Fails

Define the number of scanning intervals that the sensor has time to reach and check a device again in case a sensor query fails. Depending on the option that you select, the sensor can try to reach and check a device again several times before the sensor will show a Down status. This can avoid false alarms if the monitored device only has temporary issues. For previous scanning intervals with failed requests, the sensor will show a Warning status. Choose from:

  • Set sensor to down immediately: Set the sensor to a Down status immediately after the first failed request.
  • Set sensor to warning for 1 interval, then set to down (recommended): Set the sensor to a Warning status after the first failed request. If the following request also fails, the sensor will show an error.
  • Set sensor to warning for 2 intervals, then set to down: Set the sensor to a Down status only after three consecutively failed requests.
  • Set sensor to warning for 3 intervals, then set to down: Set the sensor to a Down status only after four consecutively failed requests.
  • Set sensor to warning for 4 intervals, then set to down: Set the sensor to a Down status only after five consecutively failed requests.
  • Set sensor to warning for 5 intervals, then set to down: Set the sensor to a Down status only after six consecutively failed requests.

icon-i-roundSensors that monitor via Windows Management Instrumentation (WMI) always wait at least one scanning interval before they show a Down status. It is not possible to immediately set a WMI sensor to a Down status, so the first option will not apply to these sensors. All other options can apply.

icon-i-roundIf you define error limits for a sensor's channels, the sensor will immediately show a Down status. No "wait" option will apply.

icon-i-roundIf a channel uses lookup values, the sensor will immediately show a Down status. No "wait" options will apply.

Schedules, Dependencies, and Maintenance Window

icon-i-roundYou cannot interrupt the inheritance for schedules, dependencies, and maintenance windows. The corresponding settings from the parent objects will always be active. However, you can define additional settings here. They will be active at the same time as the parent objects' settings.

Schedule

Select a schedule from the list. Schedules can be used to monitor for a certain time span (days or hours) every week.

icon-book-arrowsYou can create schedules, edit schedules, or pause monitoring for a specific time span. For more information, see section Account Settings—Schedules.

icon-i-roundSchedules are generally inherited. New schedules will be added to existing schedules, so all schedules are active at the same time.

Maintenance Window

Specify if you want to set up a one-time maintenance window. During a maintenance window, the current sensor and all child objects will not be monitored. They will be in a Paused status instead. Choose between:

  • Not set (monitor continuously): No maintenance window will be set and monitoring will always be active.
  • Set up a one-time maintenance window: Pause monitoring within a maintenance window. You can define a time span for a monitoring pause below and change it even for a currently running maintenance window.

icon-i-roundTo terminate a current maintenance window before the defined end date, change the time entry in Maintenance Ends to a date in the past.

Maintenance Begins

This field 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 maintenance window.

Maintenance Ends

This field 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 maintenance window.

Dependency Type

Define a dependency type. You can use dependencies to pause monitoring for an object depending on the status of another object. You can choose from:

  • Use parent: Use the dependency type of the parent device.
  • Select a sensor: Use the dependency type of the parent device. Additionally, pause the current sensor if another specific sensor is in a Down status or in a Paused status caused by another dependency. Select below.
  • Master sensor for parent: Make this sensor the master object for its parent device. The sensor will influence the behavior of its parent device: If the sensor is in a Down status, the device will be paused. For example, it is a good idea to make a Ping sensor the master object for its parent device to pause monitoring for all other sensors on the device in case the device cannot even be pinged. Additionally, the sensor will be paused if the parent group is paused by another dependency.

icon-i-roundTo test your dependencies, select Simulate Error Status from the context menu of an object that other objects depend on. A few seconds later, all dependent objects will be paused. You can check all dependencies in your PRTG installation by selecting Devices | Dependencies from the main menu bar.

Dependency

This field is only visible if you enable Select a sensor above. Click the Search button and use the object selector to select a sensor on which the current sensor will depend.

Dependency Delay (Sec.)

This field is only visible if you enable Select a sensor above. Define a time span in seconds for dependency delay.

After the master sensor for this dependency comes back to an Up status, monitoring of the dependent objects will be additionally delayed by the defined time span. This can help avoid false alarms, for example, after a server restart, by giving systems more time for all services to start up. Enter an integer value.

icon-i-round-redThis setting is not available if you set this sensor to Use parent or to be the Master sensor for parent. In this case, define delays in the parent Device Settings or in its parent Group Settings.

Access Rights

Click inherited_settings_button to interrupt the inheritance. See section Inheritance of Settings for more information.

User Group Access

Define the user groups that will have access to the selected object. A table with user groups and types of access rights is shown. It contains all user groups from your setup. For each user group, you can choose from the following access rights:

  • Inherited: Use the access rights settings of the parent object.
  • None: Users in this group cannot see or edit the object. The object neither shows up in lists nor in the device tree. Exception: If a child object is visible to the user, the object is visible in the device tree but it cannot be accessed.
  • Read: Users in this group can see the object and review its monitoring results.
  • Write: Users in this group can see the object, review its monitoring results, and edit its settings. They cannot edit access rights settings.
  • Full: Users in this group can see the object, review its monitoring results, edit its settings, and edit access rights settings.

You can create new user groups in the System Administration—User Groups settings. To automatically set all objects further down in the hierarchy to inherit this object's access rights, set a check mark for the Revert children's access rights to inherited option.

icon-book-arrowsFor more details on access rights, see section User Access Rights.

Channel Unit Configuration

Click inherited_settings_button to interrupt the inheritance. See section Inheritance of Settings for more information.

Channel Unit Types

For each type of sensor channel, define the unit in which data is displayed. If defined on probe, group, or device level, these settings can be inherited to all sensors underneath. You can set units for the following channel types (if available):

  • Bandwidth
  • Memory
  • Disk
  • File
  • Custom

icon-i-roundCustom channel types can be set on sensor level only.

More

PRTG Manual:

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 do I monitor the size of a Microsoft SQL Server Database?

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?

Knowledge Base: Which .NET version does PRTG require?

Edit Sensor Channels

To change display settings, spike filtering, and limits, switch to the sensor's Overview tab and click the gear icon of a specific channel. For detailed information, see section Sensor Channel Settings.

Notification Triggers

Click the Notification Triggers tab to change notification triggers. For detailed information, see section Sensor Notification Triggers Settings.

Others

For more general information about settings, see section Object Settings.

Sensor Settings Overview

For information about sensor settings, see the following sections:

Keywords: