<%NUMBERING1%>.<%NUMBERING2%>.<%NUMBERING3%> PRTG Manual: Oracle SQL v2 Sensor
The Oracle SQL v2 sensor monitors a database on an Oracle server and executes a defined query.
It can show the following:
- Execution time of the whole request (including connection buildup, query execution, transaction handling, disconnection)
- Execution time of a given query
- Number of rows which 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.
Oracle SQL v2 Sensor
Click here to enlarge: http://media.paessler.com/prtg-screenshots/oracle_sql_v2.png
Remarks
You cannot add this sensor type to cloud probes in PRTG in the cloud. If you want to use this sensor type, please add it to a remote probe device.
Requirement: .NET Framework
This sensor type requires the Microsoft .NET Framework to 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.
Required .NET version (with latest updates): .NET 4.0 (Client Profile is sufficient), .NET 4.5, or .NET 4.6.
For more information, please see this Knowledge Base article: 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.
Usually, a sensor connects to the IP Address or DNS Name of the parent device where you created this sensor. See the Device Settings for details. For some sensor types, you can define the monitoring target explicitly in the sensor settings. Please see below for details on available 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.
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. Top priority is at the top of a list. Choose from one star (low priority) to five stars (top priority).
|
|
Identifier
|
Enter the Oracle System ID (SID) or the SERVICE_NAME of the database the sensor will connect to. Specify below which type of identifier you use. By default, the sensor uses the SID as connection string.
The identifier is defined in the CONNECT_DATA part of the TNSNames.ora file on the Oracle instance. For example, a system ID can look like this: orcl
|
Identification Method
|
Define which type of identifier you use to connect to the database. This type depends on the configuration of your Oracle server. Choose between:
- Use SID as identifier (default): Connect to the database instance using a system ID as connection string. Enter the SID above.
- Use SERVICE_NAME as identifier: Connect to the database instance using a SERVICE_NAME as connection string. Enter the SERVICE_NAME above.
|
|
SQL Query File
|
The script will be executed with every scanning interval. The list contains SQL scripts from the database management system specific \Custom Sensors\sql subfolder 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!
For more information on how to find this path, please see Data Storage section.Demo Serveruptime.sql
SELECT AVG(UnitPrice) FROM Products. If you want to use transactions, separate the individual steps with semicolons ";".
Please be aware that with each request, the full result set will be transferred,
so use filters and limits in your query.
See also this Knowledge Base article: 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 have them 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 ? behave like common SQL variables, so consider the general rules for SQL variables. For example, you cannot use a variable for a table in the FROM statement of a query.
Example for variables usage: SELECT * FROM Table WHERE @prtg
|
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), or :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 you want to use in the query below.
|
Input Parameter
|
Use input parameter@prtg:prtg?
%sensorid%deviceid
|
Use Transaction
|
- Don't use transaction (default)
- Use transaction and always rollbackSQL query file
- Use transaction and commit on successSQL query file
|
Data Processing
|
- 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 with the query (for example, created, deleted, edited).
- Count table rows: Choose this option if you perform 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 between:
- 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 choose here also defines the method to optionally determine a value for the sensor message. For details, see setting Use Data Table Value in Sensor Message below.
Please see m
|
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 enabling it manually. 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 a sensor is created, you cannot change this value. It is shown for reference purposes only. If you need to change this, 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. Please enter a string. Channels will be generated dynamically with this name as identifier. Once a sensor is created, you cannot change this value. It is shown for reference purposes only. If you need to change this, 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 which will be used to determine the channel value in row 0. Please 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 which will be used to determine the channel value in row 0. Please 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 which will be used to determine the channel value in column 0. Please 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. Please 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 a sensor is created, you cannot change this value. It is shown for reference purposes only. If you need to change this, 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.
|
Sensor Channel #x Unit
|
This setting is only visible if you select Process data table above. Define the unit of the channel value. Once a sensor is created, you cannot change this value. It is shown for reference purposes only. If you need to change this, please add the sensor anew. Choose between:
- BytesBandwidth
- BytesMemory
- BytesDisk
- Temperature
- Percent
- TimeResponse
- TimeSeconds
- TimeHours
- Count
- CPU
- BytesFile
- SpeedDisk
- SpeedNet
- Custom
- Value Lookup
For more information about the available units, please refer to the PRTG Application Programming Interface (API) Definition for custom sensors.
To use lookups with this channel, choose Value Lookup and select your lookup file below. Do not use Custom for using lookups with this sensor!
|
Sensor Channel #x Custom Unit
|
This setting is only visible if you select Custom above. Define a unit for the channel value. Please 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 to determine a value for the sensor message is defined in 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. Please enter an integer value.
Columns 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. Please enter a string.
Columns 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. Please enter an integer value.
Columns 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. Please enter a string.
Columns 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. Please enter a string. Use the placeholder {0} at the position where the value will be added.
Example: The message is {0}
|
If Sensor Message Changes
|
Define what this sensor will do when the sensor value changes. You can 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 (Filename: "Result of Sensor [ID].txt"): Store the last result received from the sensor to the Logs (Sensor) directory in the PRTG data folder 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 overrides these files with each scanning interval.
For more information on how to find the folder used for storage, see section Data Storage.
This option is not available on cloud probes in PRTG in the cloud.
|
|
Primary Channel
|
Overview
|
Graph Type
|
Define how different channels will be shown for this sensor.
- Show channels independently (default): Show an own 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 an easy-to-read 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 Sensor Channels Settings settings).
|
Stack Unit
|
This setting is only available if stacked graphs are selected above. Choose 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 following settings are inherited from objects 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. To change a setting only for this object, disable inheritance by clicking the check mark in front of the corresponding setting name. You will then see the options described below.
|
Scanning Interval
|
Select a scanning interval (seconds, minutes, or hours) from the list. The scanning interval determines the time 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 a sensor has time reach and check a device again in case a sensor query fails. The sensor can try to re-reach and check a device several times, depending on the option you select here, before it will be set to a Down status. This helps you avoid false alarms if the monitored device has only temporary issues. For previous scanning intervals with failed requests, the sensor will show a Warning status. Choose between:
- Set sensor to "down" immediately: The sensor will show an error immediately after the first failed request.
- Set sensor to "warning" for 1 interval, then set to "down" (recommended): After the first failed request, the sensor will show a yellow warning status. If the following request also fails, the sensor will show an error.
- Set sensor to "warning" for 2 intervals, then set to "down": Show an error status only after three continuously failed requests.
- Set sensor to "warning" for 3 intervals, then set to "down": Show an error status only after four continuously failed requests.
- Set sensor to "warning" for 4 intervals, then set to "down": Show an error status only after five continuously failed requests.
- Set sensor to "warning" for 5 intervals, then set to "down": Show an error status only after six continuously failed requests.
Sensors that monitor via Windows Management Instrumentation (WMI) always wait at least one scanning interval until they show an error. It is not possible to set a WMI sensor to "down" immediately, so the first option will not apply to these sensor types. All other options can apply.
If a sensor has defined error limits for channels, it will always show a Down status immediately, so no "wait" option will apply.
If a channel uses lookup values, it will always show a Down status immediately, so no "wait" options will apply.
|
|
Inheritance for schedules, dependencies, and maintenance windows cannot be interrupted. 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, hours) every week. With the period list option it is also possible to pause monitoring for a specific time span. You can create new schedules and edit existing ones in the account settings.
Schedules are generally inherited. New schedules will be added to existing ones, so all schedules are active at the same time.
|
Maintenance Window
|
- 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.
To terminate a current maintenance window before the defined end date, change the time entry in Maintenance Ends field to a date in the past.
|
Maintenance Begins
|
This field is only visible if you enabled the 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 enabled the 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. Dependencies can be used to pause monitoring for an object depending on the status of another. You can choose between:
- Use parent: Pause the current sensor if the device, where it is created on, is in Down status, or is paused by another dependency.
- Select object: Pause the current sensor if the device, where it is created on, is in Down status, or is paused by another dependency. Additionally, pause the current sensor if a specific other object in the device tree is in Down status, or is paused by another dependency. Select below.
- Master object for parent: Make this sensor the master object for its parent device. The sensor will influence the behavior of the device, where it is created on: If the sensor is in 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 of its parent device is in Down status, or if it is paused by another dependency.
Testing your dependencies is easy! Simply choose Simulate Error Status from the context menu of an object that other objects depend on. A few seconds later all dependent objects should 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 the Select object option is enabled above. Click on the reading-glasses and use the object selector to choose an object on which the current sensor will depend.
|
Dependency Delay (Sec.)
|
Define a time span in seconds for a dependency delay. After the master object for this dependency goes back to Up status, PRTG will start monitoring the depending objects after this extra delayed. This can help to avoid false alarms, for example, after a server restart, by giving systems more time for all services to start up. Please enter an integer value.
This setting is not available if you choose this sensor to Use parent or to be the Master object for parent. In this case, please define delays in the parent Device Settings or in the superior Group Settings.
|
|
User Group Access
|
Define which user group(s) will have access to the object you're editing. 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, though not accessible.
- 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 the object's settings. They cannot edit access rights settings.
- Full: Users in this group can see the object, review its monitoring results, edit the object's 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.
For more details on access rights, please see the section User Access Rights.
|
|
Channel Unit Types
|
- Bandwidth
- Memory
- Disk
- File
- Custom
|
More
Knowledge Base: How to setup 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?
PRTG Manual:
Edit Sensor Channels
To change display settings, spike filter, and limits, switch to the sensor's Overview tab and click the gear icon of a specific channel. For detailed information, please see the Sensor Channels Settings section.
Notifications
Click the Notifications tab to change notification triggers. For detailed information, please see the Sensor Notifications Settings section.
Others
For more general information about settings, please see the Object Settings section.
Sensor Settings Overview
For information about sensor settings, please see the following sections: