PRTG Manual: Microsoft SQL v2 Sensor

SQL Query File

Select an SQL script file that includes a valid SQL statement to be executed on the server.

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

PRTG executes the script 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 file on all cluster nodes.

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

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 ";".

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

See 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)

@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, you cannot use variables 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

Data Processing

Define if you want to process data from the database:

• Just execute the query: The sensor only shows 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: If you execute a SELECT statement and want to monitor how many rows of the data table this statement returns.
• Process data table: Read and analyze the queried data table. If you select this option, the sensor counts rows with SELECT statements as well.

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

Sensor Channel #x

This setting is only visible if you select Process data table. 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 see all available settings for Channel #1 without manually enabling it. Specify how to handle all other possible channels:

• Disable: Do not add this channel to the sensor.
• Enable: Add this channel to the sensor.

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

Sensor Channel #x Name

This setting is only visible if you select Process data table. Enter a unique name for the channel. Enter a string. PRTG dynamically generates channels with this name as the identifier.

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

Sensor Channel #x Mode

This setting is only visible if you select Process data table. Define how to display the determined value in the channel:

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

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

Sensor Channel #x Unit

This setting is only visible if you select Process data table. Define the unit of the channel value:

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

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

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

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

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

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. 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. You can add additional tags to the sensor.

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

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

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

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 like tables or stored procedures exist.

SQL Server Instance

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

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

Instance Name

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

Encryption

Define encryption usage for the database connection:

• Use server defaults (default): Only encrypt the database connection if the database server enforces encryption.
• Enforce encryption but do not validate server certificate: Make sure that the database connection is encrypted.
• Enforce encryption and validate server certificate: Force encryption and validate the database server certificate. This approach provides the highest level of security and can help prevent man-in-the-middle attacks, for example.
  The sensor only validates the certificate if the database server enforces encryption.

SQL Query File

Shows the SQL script file that is executed on the server. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew.

Use Input Parameter

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

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

Passing a parameter to your SQL query file replaces the variables @prtg (Microsoft SQL, MySQL, PostgreSQL), :prtg (Oracle SQL), or ? (ADO SQL) in the SQL query, considering the general rules for SQL variables.

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

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

Use Transaction

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

• Don't 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

Shows how you process data from the database. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew.

Handle DBNull in Channel Values as

This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. Define the sensor behavior if the query returns DBNull:

• Error: Show a 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 when adding the sensor. Define how the desired cell in the database table is selected:

• Column number: Determine the channel value by using the value in row 0 of the column whose number you specify in Sensor 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 Sensor 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 Sensor Channel #x Row Number.
• Key value pair: Determine the channel value by searching in column 0 for the key you specify in Sensor 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 sensor channels.

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.

See section Monitoring Databases for an example for channel value selection.

Sensor Channel #x

This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. The setting shows if the sensor channel is disabled. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew.

Sensor Channel #x Name

This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. Enter a unique name for the channel. Enter a string. PRTG dynamically generates channels with this name as identifier. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew.

Sensor Channel #x Column Number

Sensor Channel #x Column Name

Sensor Channel #x Row Number

Sensor Channel #x Key

This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. Depending on the option you selected under Select Channel Value by, enter the respective value in the respective field:

• Sensor Channel #x Column Number: Provide the number of the column that is used to determine the channel value in row 0. Enter an integer value.
• Sensor Channel #x Column Name: Provide the name of the column that is used to determine the channel value in row 0. Enter a string.
• Sensor Channel #x Row Number: Provide the number of the row that is used to determine the channel value in column 0. Enter an integer value.
• Sensor Channel #x Key: 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 is used to determine the channel value. Enter a string.

Sensor Channel #x Mode

This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. The setting shows how you want to display the determined value in the channel. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew.

Sensor Channel #x Unit

This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. Define the unit of the channel value:

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

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

Sensor Channel #x Custom Unit

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

Sensor Channel #x Value Lookup

This setting is only visible if you select the sensor channel unit Value Lookup. 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 when adding the sensor. 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.

Sensor Message Column Number

Sensor Message Column Name

Sensor Message Row Number

Sensor Message Key

This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor and if you enable Use Data Table Value in Sensor Message. Depending on the option you selected under Select Channel Value by, enter the respective value in the respective field:

• Sensor Message Column Number: 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: 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: 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: 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.

Columns and rows start with index 0.

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}

The number sign (#) is not supported in sensor messages. If a message contains a number sign, the message is clipped at this point.

If Sensor Message Changes

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

Define what the sensor does when the sensor value changes:

• Ignore changes (default): Take no action on change.
• Trigger 'change' notification: Send an internal message indicating that the sensor 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 does with the sensor results:

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

This option is not available when the sensor runs on the hosted probe of a PRTG hosted by Paessler instance.

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.

You 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 are 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 generates a graph that visualizes the different components of your total traffic.
  This option cannot be used in combination with manual Vertical Axis Scaling (available in the channel settings).

Stack Unit

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

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

Sensors 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 does not apply to these sensors. All other options can apply.

If you define error limits for a sensor's channels, the sensor immediately shows a Down status. No "wait" option applies.

If a channel uses lookup values, the sensor immediately shows a Down status. No "wait" options apply.

Schedule

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

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

Schedules are generally inherited. New schedules are added to schedules that you already set up, 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 selected object and all child objects are not monitored. They are in a Paused status instead. Choose between:

• Not set (monitor continuously): No maintenance window is set and monitoring is always 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 an active maintenance window.

To terminate an active 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 end 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 a different object. You can choose from:

• Use parent: Use the dependency type of the parent object.
• Select a sensor: Use the dependency type of the parent object. Additionally, pause the current object if a specific sensor is in a Down status or in a Paused status caused by another dependency.

• Master sensor for parent: Make this sensor the master object for its parent device. The sensor influences the behavior of its parent device: If the sensor is in a Down status, the device is 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 is paused if the parent group is paused by another dependency.

To 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 are paused. You can check all dependencies under Devices | Dependencies in 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 object 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 is additionally delayed by the defined time span. This can prevent false alarms, for example, after a server restart, by giving systems more time for all services to start up. Enter an integer value.

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

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:

• Inherited: Inherit the access rights settings of the parent object.
• No access: Users in this user group cannot see or edit the sensor. The sensor neither shows up in lists nor in the device tree.
• Read access: Users in this group can see the sensor and view its monitoring results. They cannot edit any settings.
• Write access: Users in this group can see the sensor, view its monitoring results, and edit its settings. They cannot edit its access rights settings.
• Full access: Users in this group can see the sensor, view its monitoring results, edit its settings, and edit its access rights settings.

For more details on access rights, see section Access Rights Management.

Channel Unit Types

For each type of 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

Custom channel types can be set on sensor level only.