PRTG Manual: Oracle SQL v2 Sensor
The Oracle SQL v2 sensor monitors a database on an Oracle server and executes a query.
The sensor can also process the data table and show the values that you define in individual channels.
For a detailed list and descriptions of the channels that this sensor can show, see section Channel List.
- Dutch: Oracle SQL v2
- French: Oracle SQL v2
- German: Oracle SQL v2
- Japanese: Oracle SQL v2
- Portuguese: Oracle SQL v2
- Russian: Oracle SQL v2
- Simplified Chinese: Oracle SQL v2
- Spanish: Oracle SQL v2
- This sensor has a high performance impact. We recommend that you use no more than 200 of this sensor on each probe.
- This sensor requires that the Structured Query Language (SQL) query is stored in a file on the probe system. In a cluster, copy the file to every cluster node.
- This sensor requires .NET 4.7.2 or later from Microsoft on the probe system.
- This sensor supports Oracle database servers as of version 10.2
- This sensor supports IPv6.
- This sensor can use lookups. Select Lookup as Channel #x Unit and define the lookup file in Channel #x Lookup.
- Define the credentials for database management systems in settings that are higher in the object hierarchy.
- 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 Monitor instance. If you want to use this sensor, add it to a remote probe device.
Requirement |
Description |
---|---|
This sensor requires .NET 4.7.2 or later to be installed on the probe system (on every cluster node, if on a cluster probe). If the framework is missing, you cannot create this sensor. For more information, see the Knowledge Base: Which .NET version does PRTG require? |
Setting |
Description |
---|---|
Sensor Name |
Enter a 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. If the name contains angle brackets (<>), PRTG replaces them with braces ({}) for security reasons. For more information, see the Knowledge Base: What security features does PRTG include? |
Parent Tags |
The tags that the sensor inherits from its parent device, parent group, and parent probe. This setting is for your information only. You cannot change it. |
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. It is not possible to enter tags with a leading plus (+) or minus (-) sign, nor tags with parentheses (()) or angle brackets (<>). For performance reasons, it can take some minutes until you can filter for new tags that you added. The sensor has the following default tags that are automatically predefined in the sensor's settings when you add the sensor:
|
Priority |
Select a priority for the sensor. This setting determines the position of the sensor in lists. The highest priority is at the top of a list. Choose from the lowest priority () to the highest priority (). |
Setting |
Description |
---|---|
Identifier |
Enter the Oracle System ID (SID) or the SERVICE_NAME of the database that the sensor connects to. Specify the Identification Parameter below. By default, the sensor uses the SID as the connection string. You can find the SID or SERVICE_NAME in the CONNECT_DATA part of the TNSNames.ora file on the Oracle server. For example, a SID can look like this: orcl |
Identification Parameter |
Define the type of identifier that you want to use to connect to the database:
The type of identifier that you need to use depends on the configuration of the Oracle server. |
Setting |
Description |
---|---|
SQL Query File |
Select the SQL query file that includes a valid SQL statement that the sensor executes on the server with every scanning interval. The list contains SQL scripts from the \Custom Sensors\sql subfolder of the PRTG program directory on the probe system. Store your script there. If you use the script on a cluster probe, you must store the script on all cluster nodes. A correct expression in the file could be: SELECT AVG(UnitPrice) FROM Products. If you want to use transactions, separate the individual steps with semicolons ";". Note that with each request, PRTG transfers the full result set, so use filters and limits in your query. The demo script Demo Serveruptime.sql is available by default. You can use it to monitor the uptime of the target server. See also the Knowledge Base: Why do I have to store SQL sensor queries and custom scripts in files on the probe computer? You cannot change this value after sensor creation. |
Input Parameter Handling |
Define if you want to pass a parameter to the SQL query file:
|
Input Parameter |
This setting is only visible if you select 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 more information, 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. |
Transaction Handling |
Define if you want to use transactions and if they affect the database content:
|
Data Processing |
Define whether the sensor processes data from the database:
You cannot change this value after sensor creation. |
DBNull Handling |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Define the sensor behavior if the query returns DBNull:
|
Select Channel Value by |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Define how to select the desired cell in the database table:
Defining how the desired cell in the database table is selected is necessary to configure the cells that are used in the channels. The option you select here also defines the method of how to optionally determine a value for the sensor message. For more information, see setting Use Data Table Value in Message. For an example for channel value selection, see section Monitoring Databases. |
Channel #2 - #10 |
This setting is only visible if you select Process data table above. You can define up to 10 channels. You must define at least one channel, so you see all available settings for Channel #1. Specify how to handle all other possible channels:
It is not possible to enable or disable channels after sensor creation. |
Channel #x Name |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Enter a name for the channel. Enter a string. The sensor dynamically generates channels with this name as identifier. If the name contains angle brackets (<>), PRTG replaces them with braces ({}) for security reasons. For more information, see the Knowledge Base: What security features does PRTG include? |
Channel #x Column Number |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Column number for the setting Select Channel Value by. Provide the number of the column to use to determine the channel value in row 0. Enter an integer. |
Channel #x Column Name |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Column name for the setting Select Channel Value by. Provide the name of the column to use to determine the channel value in row 0. Enter an integer. |
Channel #x Row Number |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Row number for the setting Select Channel Value by. Provide the number of the column to use to determine the channel value in row 0. Enter an integer. |
Channel #x Key |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Key value pair for the setting Select Channel Value by. Provide the key to search for in column 0 of the data table. The value in column 1 of the same row where the key value was found to use to determine the channel value. Enter a string. |
Channel #x Mode |
This setting is only visible if you select Process data table above. Define how to display the determined value in the channel:
You cannot change this value after sensor creation. |
Channel #x Unit |
This setting is only visible if you select Process data table above. Define the unit of the channel value:
For more information about the available units, see section Custom Sensors. To use lookups with this channel, select Lookup and define the lookup file in Channel #x Lookup. Do not use Custom if you use lookups with this sensor. It is not possible to use the unit Lookup in combination with the Difference mode. You are not able to create the sensor in this case. |
Channel #x Custom Unit |
This setting is only visible if you select Custom above. Define a unit for the channel value. Enter a string. |
Channel #x Lookup |
This setting is only visible if you select Lookup above. Select a lookup file that you want to use with this channel. |
Use Data Table Value in Message |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Define if the sensor message shows a value from the data table:
The method of how to determine a value for the sensor message is defined in the setting Select Channel Value by above. |
Message Column Number |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Column name for the setting Select Channel Value by, and if you select Enable above. Enter the number of a column. The sensor message shows the value in row 0 of this column. Enter an integer. |
Message Column Name |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Column name for the setting Select Channel Value by, and if you select Enable above. Enter the name of a column. The sensor message shows the value in row 0 of this column. Enter a string. Columns start with index 0. |
Message Row Number |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Row number for the setting Select Channel Value by, and if you select Enable above. Enter the name of a column. The sensor message shows the value in row 0 of this column. Enter the number of a row. The sensor message shows the value in column 0 of this row. Enter an integer. Rows start with index 0. |
Message Key |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Key value pair for the setting Select Channel Value by, and if you select Enable 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. |
Message |
This setting is only visible if you select Enable above. 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 Message Changes |
This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Define what the sensor does when its message changes:
|
Result Handling |
Define what PRTG does with the sensor result:
In a cluster, PRTG stores the result in the PRTG data directory of the master node. |
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. You can set a different primary channel later by clicking below a channel gauge on the sensor's Overview tab. |
Graph Type |
Define how different channels are shown for this sensor:
|
Stack Unit |
This setting is only visible if you select Stack channels on top of each other above. 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 these settings are inherited from objects that are higher in the hierarchy. We recommend that you change them centrally in the root group settings if necessary. To change a setting for this object only, click under the corresponding setting name to disable the inheritance and to display its options.
For more information, see section Inheritance of Settings.
For more information, see section Root Group Settings, section Scanning Interval.
Schedules, Dependencies, and Maintenance Window
You cannot interrupt the inheritance for schedules, dependencies, and maintenance windows. The corresponding settings from the parent objects are always active. However, you can define additional schedules, dependencies, and maintenance windows. They are active at the same time as the parent objects' settings.
For more information, see section Root Group Settings, section Schedules, Dependencies, and Maintenance Window (default).
For more information, see section Root Group Settings, section Access Rights.
Which channel units are available depends on the sensor type and the available parameters. If no configurable channels are available, this field shows No configurable channels.
For more information, see section Root Group Settings, section Channel Unit Configuration.
Which channels the sensor actually shows might depend on the target device, the available components, and the sensor setup.
Channel |
Description |
---|---|
Affected Rows |
The number of rows that were addressed by the query (including SELECT statements if you process data tables) |
Downtime |
In the channel table on the Overview tab, this channel never shows any values. PRTG uses this channel in graphs and reports to show the amount of time in which the sensor was in the Down status |
Execution Time |
The execution time of the entire request (including connection buildup, query execution, transaction handling, disconnection) This channel is the primary channel by default. |
Query Execution Time |
The execution time of the specified query |
KNOWLEDGE BASE
How to set up the SQL v2 sensors in PRTG? Is there a guide?
How can I monitor strings from an SQL database and show a sensor status depending on it?
How can I monitor error tables in SQL databases?
Why do I have to store SQL sensor queries and custom scripts in files on the probe computer?
Which .NET version does PRTG require?
What security features does PRTG include?