|
Objective
The aim of this tool is to execute SQL queries on most of the database servers currently available on the market (Microsoft SQL, MySQL and Oracle for instance) and monitor their return output within your PATROL environment.
You can then run string or numeric value searches on the return output to get/monitor the result in myriad ways. You need no longer run separate SQL queries.
Method (Summary)
If the application you wish to monitor uses a database server, you can test this database by sending applicative queries to the database server, or by testing the content of some critical application tables. Once you specify an SQL query, you can search for strings in the result set and build graphs using the numerical values returned. Queries can be set up to perform complex tasks with just the click of a button, and they can be saved for future use.
As query results are stored by Monitoring Studio in a pipe-separated table format, it is easy to specify strings to be searched or numeric values to be extracted from a database query. Please refer to the string search and numeric value extraction sections for more information.
Example
How the database query result is formatted and then searched for strings and numeric values:
|
Create or edit a database query analysis
To create a new database query analysis, right-click the Application/Container icon in the PATROL Console and select KM Commands > New > Database Query analysis…
To edit an existing Database query analysis, right-click the Database Query analysis icon in the PATROL Console and select KM Commands > Edit.
Step 1: Selecting the database type
Database Query Analysis Wizard — Welcome Page
| • | Select a database type: Specify here whether the database used by your application is an Oracle Database Server, MySQL Server, a Microsoft SQL Server, or any Other database (supporting only ODBC connections). |
| • | Let me set up the database connection method: Check this option to select the connection method you wish to use to access the database. A list of available connection methods will be displayed in next wizard's panel. |
Click Next to continue.
Step 2: Selecting the connection method
Monitoring Studio searches for an existing Database Query Analysis in the configuration for the selected database type. If a Database Query Analysis is found, Monitoring Studio uses these settings to access the database. If no Database Query Analysis is found, the following panel is displayed whether the Let me set up the database connection method is selected or not.
Database Query Analysis Wizard — Database Connection Method Selection
| • | Command line utility: Select this option to use the command line tool developed by the database provider to manage access to a database. This method is by far the fastest but requires the command line tools to be installed on the local server and properly set. These command line tools are: |
| ▪ | Microsoft SQL Server: OSQL |
| ▪ | Oracle Database Server: Oracle SQL*Plus |
| • | Java Database Connectivity (JDBC): Select this option to use JDBC to perform the query to the database. This method is the slowest and requires Java to be installed on your local server. |
| • | Open Database Connectivity (ODBC): Select this option to use ODBC to perform the query to the database. This method is known as the most flexible. However, it requires the proper driver to be installed on the local server as well as Java for UNIX/Linux servers. |
 | Additional information: |
| • | SQL queries on Oracle Database running on Windows servers can only be performed locally with the ODBC connection method; for UNIX, SQL queries are not limited to the local host but a specific driver must be installed on the server where the agent is running. |
| • | Performing an SQL Query on a Microsoft SQL database installed on a UNIX Server is only possible with JDBC and ODBC connection methods since Microsoft does not provide OSQL for UNIX. |
The next steps depend on the database server and the connection method selected:
Step 3: Connection Information
The two following steps of he Database Query Analysis wizard are common to all database queries and connection methods. They follow Step 3 that depends on the database server and the connection methods you have selected (see above).
Step 4 - Defining the query to be executed
Database Query Analysis Wizard — Query Definition Page
| • | SQL Query to execute: Single line SQL statement to be executed by the Oracle server. This can be also the path to an existing SQL script file. |
| Use the following syntax if you want to execute the SQL query from a file: "@@ <file name>". Only supported with Command Line connection method. |
| • | Query timeout: When the execution exceeds the given timeout (in seconds), the ConnectionStatus parameter goes into alert. |
Clicking Next brings you to the last step of the wizard with the Monitoring Studio settings.
Example
| • | Oracle service name: BACKENDDB |
| • | SQL query: SELECT COUNT(*) FROM PendingCustomers |
| • | Behavior: Will connect to the BACKENDDB Oracle server instance using the sysApp database account and ask the server to return the number of rows in the Pending Customers table. This query should give a number representing the number of waiting customers for the application. |
|
Step 5 -Monitoring Studio settings
The final step of the wizard allows you to enter information related to the query labeling and the threshold settings.
Database Query Analysis Wizard — Settings Page
| • | Object display name: Label displayed in the PATROL Console |
| • | Object internal identifier (ID): PATROL internal identifier of this monitored object |
What thresholds do you want to set for the newly created instance? A drop-down list allows you to select the mode of setting alert thresholds:
| • | Use default thresholds: Uses the default thresholds set by Monitoring Studio |
| • | Set custom thresholds: Allows you to customize the thresholds for all parameters of the instance |
| • | Use default thresholds and customize them: Sets the default Monitoring Studio thresholds on certain parameters (see list of parameters with default thresholds) and then allows you to customize any/all of them. This is mainly intended to help save time if you wish to customize the thresholds of just one of many parameters for the instance, and leave the default settings for the others. |
| If you select Use default thresholds and customize them; on clicking Finish, the Set Thresholds panel will appear, certain parameters for the instance may appear with an asterisk symbol - indicating that they already have thresholds. You can then customize (any/all) the thresholds of the parameters as per your specific needs. |
| • | Do not set any thresholds for now: No thresholds will be set on any parameter of the instance, and as a result no alerts will be triggered. Monitoring Studio will poll the object and return the output of the polling – but will not raise any alerts until you set thresholds. |
 | Thresholds can be set or modified at anytime by right-clicking on the instance > KM commands > Set Thresholds. |
Database query analysis objects are instances of the SW_DB_QUERIES class.
See Also
Numeric Value Extraction
String Search
Dynamic Object Builder
SW_DB_QUERIES
| 
