PRTG Manual: Oracle SQL v2 Sensor
The Oracle SQL v2 sensor monitors a database on an Oracle 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.
- Requires .NET 4.7.2 or later on the probe system.
- 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.
- 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 Oracle database servers version 10.2 or higher.
- See section Monitoring Databases for an example for channel value selection.
- 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?
You 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.
This sensor requires the Microsoft .NET Framework. .NET 4.7.2 or later must be installed on the probe system, 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.
For more information, see the Knowledge Base: Which .NET version does PRTG require?
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.
The following settings in the Add Sensor dialog differ in comparison to the sensor's Settings tab.
Data |
|
---|---|
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 database management system-specific subfolder \Custom Sensors\sql of your PRTG installation. Store your script there. If you use the script on a remote probe, you must store the file on the system running the remote probe. If you use the script on a cluster probe, you must store the file on all servers running a cluster node.
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 ";".
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.
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:
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:
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:
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:
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.
|
On the details page of a sensor, click the Settings tab to change its settings.
Usually, 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.
|
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). |
sqlsensor
Database Specific |
|
---|---|
Identifier |
Enter the Oracle System ID (SID) or the SERVICE_NAME of the database the sensor connects to. Specify which type of identifier you use in Identifier Type. By default, the sensor uses the SID as connection string.
|
Identifier Type |
Define which type of identifier you want to use to connect to the database:
|
Data |
|
---|---|
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:
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.
|
Use Transaction |
Define if you want to use transactions and if they affect the database content:
|
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:
|
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:
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.
|
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 |
This setting is only visible if you select the channel value by 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 |
This setting is only visible if you select the channel value by Column number. 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 |
This setting is only visible if you select the channel value by 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 |
This setting is only visible if you select the channel value by Key value pair. 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:
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:
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 the channel value by Column number and enable Use Data Table Value in Sensor Message above. 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 the channel value by Column name and enable Use Data Table Value in Sensor Message above. 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 the channel value by Row number and enable Use Data Table Value in Sensor Message above. 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 the channel value by Key value pair and enable Use Data Table Value in Sensor Message 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.
|
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 when adding the sensor. Define what the sensor does when the sensor value changes:
|
Sensor Result |
Define what PRTG does with the sensor results:
|
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.
|
Graph Type |
Define how different channels are shown for this sensor:
|
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. |
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 then see the options described below.
Scanning Interval |
|
---|---|
Click |
|
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:
|
Schedules, Dependencies, and Maintenance Window |
|
---|---|
|
|
Schedule |
Select a schedule from the list. Schedules can be used to monitor for a certain time span (days or hours) every week.
|
Maintenance Window |
Specify if you want to set up a one-time maintenance window. During a maintenance window, the current object and all child objects are not monitored. They are in a Paused status instead. Choose between:
|
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 another object. You can choose from:
|
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 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.
|
Access Rights |
|
---|---|
Click |
|
User Group Access |
Define the user groups that 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:
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.
|
Channel Unit Configuration |
|
---|---|
Click |
|
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):
|
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?
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.
Click the Notification Triggers tab to change notification triggers. For detailed information, see section Sensor Notification Triggers Settings.
For more general information about settings, see section Object Settings.
For information about sensor settings, see the following sections: