PRTG Manual: Microsoft SQL Sensor

This Sensor Type Is Deprecated!

This sensor type is deprecated. We provide the documentation in this section for your information only. Running sensors of this type continue monitoring but you are not able to add sensors of this type anew. We strongly recommend that you replace all sensors of this type with the according v2 version.

See the following article for details:

Knowledge Base: Migration Path for Deprecated SQL Sensors

Alternative Sensor Type

Please use the Microsoft SQL v2 Sensor instead.

The Microsoft SQL sensor monitors a Microsoft SQL server. It shows the response time, the number of records, and the value. The sensor can monitor if the database server accepts connections, processes requests, and returns an expected result when executing a custom SQL command.

The following SQL servers are supported: SQL Server 2008, SQL Server 2005 (including SQL Server Express / Server Compact Editions), SQL Server 2000, SQL Server 7 and MSDE (requires Object Linking and Embedding, Database (OLE DB) installed on the probe system that accesses the server)

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 do not see all setting fields in this dialog. You can change (nearly) all settings in the sensor's Settings tab later.

Sensor Settings

Click the Settings tab of a sensor to change its settings.

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

Setting

Description

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. There are default tags that are automatically predefined in a sensor's settings when you add a sensor.

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

SQL-Server Specific Data

Timeout (sec.)

Enter a timeout in seconds for the request. If the reply takes longer than this value the request is canceled and an error message is triggered.

Instance

Enter the SQL server instance's name if you want to connect to a "named instance". For example, such an instance's name could be SQLEXPRESS. Note: Sometimes you will see connection strings like SQLSERVER\SQLINSTANCE in database clients. The first part is the server name configured under the general server settings. The second part refers to the instance name mentioned above. Never enter this complete string in this PRTG form, merely provide the second part (without the backslash).

Enter a string or leave the field empty.

Port

Define the port for the SQL connection:

  • Automatic: Automatically choose the right port.
  • Manual: Use a specific port. Define below. Standard value is 1433.

Port

This field is only visible if manual port selection is enabled above. Define the port number for the SQL connection.

Enter an integer value.

Database

Database

Enter the name of the SQL database the sensor will connect to. For example, such a database's name could be Northwind

Authentication

Define the kind of authentication used for the connection to the database:

  • SQL Server: Use credentials specific to the SQL server. Enter them below.
  • Windows Authentication: Use the Windows credentials defined for the parent device this sensor is created on. Note: For successful authentication, the defined Windows credentials must be known to the SQL server, and the SQL server and the probe system must be in the same domain.

User

This field is only visible if SQL server authentication is enabled above. Enter the username that will be used for the database connection.

Password

This field is only visible if SQL server authentication is enabled above. Enter the password that will be used for the database connection.

Data

SQL Expression

Enter a valid SQL statement to execute on the server. In your SELECT statement, specify the field name explicitly. Do not use SELECT *! For example, a correct expression could be: SELECT ProductName FROM Northwind.dbo.Products. When a cursor is returned (i.e. with a SELECT statement), only the first row of data will be processed.

Note: Executing stored procedures is currently not supported.

Record Count

If you want to know how many records in your database were affected by your SQL expression as defined above, you can count the number of concerned records. Choose between:

  • Do not count number of records: Disable the counting of affected records. The "Record Count" channel always shows "0" if you choose this option.
  • Count number of records: Show the number of affected records in the "Record Count" channel.
    Note: Enabling this option might increase the execution time of this sensor.

Post-Processing

If the SQL expression returns a result set, define if you want to further process it:

  • Ignore result set: Do not process the data returned.
  • Process numerical result: Regard the result set returned as integer or float. You can set the sensor to Warning or Down status for specific thresholds. Define below.
  • Process string result: Regard the result set returned as a string. You can set the sensor to error for certain substrings contained or missing. Define below.
  • Monitor for changes: Check if the result set has changed since last sensor scan. 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 a sensor value changes.

Condition "Warning"

This field is only visible if you Process numerical result above. Define a condition of the returned numeric SQL result set that sets the sensor to a Warning status:

  • none: Do not set the sensor to Warning status for any condition.
  • equals: Result set must be the same as the value provided below.
  • is above: Result set must be above the value provided below.
  • lower than: Result set must be below the value provided below.
  • does not equal: Result set must be unequal to the value provided below.

Note: This threshold check the SQL result set only. You can set additional thresholds in the sensor's channel settings.

Value

This field is only visible if you enable Process numerical result above. Enter a value of the returned numeric SQL result set that sets the sensor into a Warning status. Enter an integer or float value.

Condition "Down"

This field is only visible if you enable Process numerical result above. Define a condition of the returned numeric SQL result set that sets the sensor to a Down status:

  • none: Do not set the sensor to Down status for any condition.
  • equals: Result set must be the same as the value provided below.
  • is above: Result set must be above the value provided below.
  • lower than: Result set must be below the value provided below.
  • does not equal: Result set must be unequal to the value provided below.

Note: This threshold check the SQL result set only. You can set additional thresholds in the sensor's channel settings.

Value

This field is only visible if you enable Process numerical result above. Enter a value of the returned numeric SQL result set that sets the sensor to a Down status.

Enter an integer value.

Response Must Include

This field is only visible if you enable Process string result above. Enter a string that must be part of the returned SQL string. If this is not the case, the sensor shows a Down status.

Enter a string or leave the field empty.

Response Must Not include

This field is only visible if you enable Process string result above. Enter a string that must not be part of the returned SQL string. If this is the case, the sensor shows a Down status.

Enter a string or leave the field empty.

Sensor Display

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.

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

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 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 then see the options described below.

Scanning Interval

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

Setting

Description

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.

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

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

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

Schedules, Dependencies, and Maintenance Window

i_round_blueYou 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 settings here. They are active at the same time as the parent objects' settings.

Setting

Description

Schedule

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

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

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

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

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

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 b_inherited_enabled to interrupt the inheritance. See section Inheritance of Settings for more information.

Setting

Description

User Group Access

Define the user groups that have access to the object. 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 object. The object neither shows up in lists nor in the device tree.
    i_round_blueThere is one exception: If a user in this user group has access to a child object, the parent object is visible in the device tree but users in this user group cannot access it.
  • Read access: Users in this group can see the object and view its monitoring results. They cannot edit any settings.
  • Write access: Users in this group can see the object, view its monitoring results, and edit its settings. They cannot edit its access rights settings.
  • Full access: Users in this group can see the object, view its monitoring results, edit its settings, and edit its access rights settings.

To automatically set all child objects to inherit this object's access rights, enable the Revert children's access rights to inherited option.

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

Edit 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: