Adding an SQL Query/Stored Procedure-based Test

Many applications store critical statistics in a database. To simplify the writing of tests, the Integration Console includes an SQL Query test type that allows a user to include a new test that retrieves measures by simply executing a SQL query / stored procedure on the database, instead of writing elaborate java code. The procedure for achieving this is explained in the following sections:

Using a SQL Query

To understand this concept better, consider an example. In this example, a new test named SqlTest of type Sql Query will be created, which will be configured to measure the number of current users to a custom application. This application is executing on a host with IP address 192.168.10.8, and uses an Oracle database executing on the same system (192.168.10.8) as its back end. This example will demonstrate how application-specific metrics stored in the database can be retrieved using a SQL query and integrated into the eG Enterprise system. 

To add this test, first, click the Add New Test button in the integration console - test page (see Figure 2). In the new test details page (see Figure 1) that appears next, specify the following:

  • Test name: SqlTest_ex

    Note:

    While adding a new test using the Integration Console, ensure that the Test name always ends with _ex. If not, an error message (see Figure 3) will appear upon clicking the Add button in Figure 1.

  • Duplicate: Since the new test is not a duplicate of any existing test, set the Duplicate flag to No.

  • Execution: Internal, as the test is to be executed by an internal agent

    Note:

    Using the Integration Console plugin, you can add an internal or an external test, but you cannot add tests that need to be run by a remote agent – i.e., tests that need to be executed in an agentless manner.

  • Port based: Since the Webmall application is not listening on a specific TCP port, select No against the Port based input selection.
  • Test type: Sql Query
  • DB type: If the query is to be executed on an Oracle database, select Oracle from this list box. On the other hand, if the query is to be executed on an MS SQL database, select MsSql from the list box. Similarly, if the query is to be retrieve data from a Sybase, MySql, DB2, or a PostgreSQL database, then, choose Sybase, MySql, DB2, or PostGres from the list box. For our example, select Oracle. Then, click the Add button.

Figure 1 : Providing the new test details

Next, using Figure 2, the parameters to the test need to be specified. To add the new parameter, click the Add New Parameter button in Figure 2.

Figure 2 : Modifying the details of the SqlTest_ex

As the SqlTest_ex will not be taking any parameters, simply proceed to configure the measures for this test by clicking the Measure tab page in Figure 2. When Figure 3 appears, click the Add New Measure in Figure 3 to open the new measure details pop-up (see Figure 4).

Figure 3 : The Measure tab page reporting that no measures have been configured for the SQL query-based test

Figure 4 : Adding the CurrentUsers measure for the SQL query-based test

Figure 4 shows how a measurement of the Sql Query test type is specified using the new measure details pop-up. To generate measures pertaining to the number of users, create a measure named CurrentUsers. Accordingly, specify the Measure name, the Database column size, the Unit, Conversion Factor, and the Process method (see Figure 4).

Note:

Since we do not want to associate an alarm description with this measure, leave the Alarm display string field blank (see Figure 4).

Finally, click on the Add button in Figure 4 to add the measure. When prompted to add more measures for the SQLTest_ex, click No to stop configuring any more measures (see Figure Figure 10). Clicking No will instantly lead you to the Generate tab page (see Figure 5).  

Figure 5 : Specifying the Sql query associated with the SqlTest_ex

In Figure 5, specify the SQL query that will, on execution, fetch the number of current users. Finally, click the Generate button to integrate the test’s implementation into the eG Enterprise system. You can even click on the Add Help button therein to create and upload Admin and Monitor help pages for the new test. To know how, refer to Adding a Custom Configuration Test.

An Sql Query test can be both descriptor-based and non-descriptor based. For example, the following query returns a descriptor-based ouput. The query retrieves from a table named metatest the number of records that carry the same value in a field named info.

select substr(info, 2), count(*) from metatest where info<>’+’ group by info

For a descriptor-based test, the first value of the results of the query must be a string. The other results should be integer or double values. If the first value of a query’s result is not a string, the test is not descriptor-based and only the first row of the result set will be used.

An Sql query can return multiple outputs. For example:

select read_rate, write_rate from disktest where msmt_time=(select max(msmt_time) from disktest) and info=’+/’

When a test’s measurements are successfully configured, the eG Enterprise system prompts the user to specify the default threshold settings for each of the measurements made by the newly added test (see Figure 6).

Figure 6 : Specifying the threshold values of the CurrentUsers measure

Using Stored Procedure

Typically, large or complex processing that might require the execution of several SQL statements is moved into stored procedures. You might choose a stored procedure over a SQL query, if:

  • you want to execute multiple SQL queries, simultaneously
  • you not only want to query metrics from the database, but also intend to perform mathematical computations on the result set and display the net output in the eG monitor interface.

To help you clearly understand how a stored procedure can be used to build a test’s functionality, let us take another example. In this example, we would be attempting to create an ‘info-based’ test, which will execute a stored procedure on an MS SQL server database; this stored procedure will take a Host IP from the user, calculate the average CPU utilization of every processor on the given host, and report the computations to the eG manager. 

To add the new test, first click the Test option in the Integration Console tile. Then, click on the Add New Test button in the integration console - test page that appears (see Figure Figure 2) that opens next. In the new test details page (see Figure 7), specify the following:

  • Test name: AvgCpuUtilTest_ex

    Note:

    While adding a new test using the Integration Console, ensure that the Test name always ends with _ex. If not, an error message will appear upon clicking the Add button in Figure 7.

  • Duplicate: Since the new test is not a duplicate of any existing test, set the Duplicate flag to No.

  • Execution: Internal, as the test is to be executed by an internal agent

    Note:

    Using the Integration Console plugin, you can add an internal or an external test, but you cannot add tests that need to be run by a remote agent – i.e., tests that need to be executed in an agentless manner.

  • Port based: Select No against the Port based input selection.
  • Test type: Sql Query
  • DB type: For our example, select MsSQL from the list box. The other options are Oracle, Sybase, MySql, DB2, and PostGres.

Figure 7 : Providing the details of the SQL stored procedure-based test

Once the test details are specified in the Test tab page of Figure 7, click the Add button to add the new test. This will automatically take you to the Parameter tab page, where the parameters to the test need to be specified. To add new test parameters, click the Add New Parameter button in Figure 8. This will invoke the new test parameters pop-up (see Figure 8).

Figure 8 : Adding a new test parameter for the SQL stored procedure-based test

Since the AvgCpuUtilTest_ex takes the IP address of a monitored host as its parameter, specify TargetHost against Parameter, and click the Add button in the new test parameters pop-up. eG will now request you to confirm whether/not you want to add more parameters to the test. As the AvgCpuUtilTest_ex in our example does not take any more parameters, click No in the message box to stop adding parameters. You will now return to the Parameter tab page, where you can quickly review your parameter settings (see Figure 9).

Figure 9 : Reviewing the parameter specification of the SQL stored procedure-based test

Then, click the Measure tab page to configure the measures of the test. Figure 10 will then appear.

Figure 10 : The Measure tab page indicating that no measures have been configured yet for the SQL stored procedure-based test

Click the Add New Measure button to add a new measure for the test. The new measure details (see Figure 11) will then pop up.  To generate a measure that indicates the average CPU utilization of a processor, create a measure with the Measure name, Avg_cpu_util. Also provide the Database column size, Unit, Conversion Factor and the Process method specifications as indicated by Figure 11.

Figure 11 : Adding the Avg_cpu_util measure of the SQL stored procedure-based test

Likewise, specify an Alarm display string similar to the one provided in Figure 11. 

After specifying all the required details, click on the Add button in Figure 11 to add the measure. You will then be prompted to indicate whether/not you want to continue adding measures for the test. Since the AvgCpuUtilTest_ex in our example does not report any more measures, click No against the prompt to stop adding more measures. This will lead you to Figure 12.

Figure 12 : Specifying the stored procedure associated with the Ag_cpu_util measure

Against the SQL query field in Figure 12, issue the command for invoking a stored procedure named avgCpuUtil, and click the Generate button (see Figure 12).

The avgCpuUtil stored procedure has been specifically created for the purpose of our example, and performs the following tasks:

  • Retrieves the CPU utilization metrics for every processor that a specified host supports, from the systemtest table in the MS SQL database
  • Computes the average of the CPU utilization metrics per processor

Typically, the syntax for the command to be issued to execute a stored procedure is: StoredProcedurename. In our example however, the stored procedure accepts a Host IP from the user and retrieves the CPU usage statistics that correspond to the given IP address. To execute a stored procedure that supports input parameters/arguments (such as the one in our example), you should use the command: StoredProcedureName <<Argument>>. In the case of our example therefore, the command would be: avgCpuUtil <<TargetHost>>, where avgCpuUtil is the name of the stored procedure, and TargetHost is the name of the parameter that the procedure supports.

Note:

The arguments/parameters that are passed to a stored proocedure are case-sensitive, and should always be enclosed within angular brackets (<<>>). This implies that the Argument provided in the command should be of the same case as the parameter configured for the AvgCpuUtilTest_ex in Figure 8. Therefore, the parameter TargetHost should be expressed as <<TargetHost>> in the command.

A stored procedure that is executed on an MS SQL database can take any number of arguments, and returns a result set. A result set with multiple columns, where the first column contains character values, is said to be ‘info-based’. On the other hand, if a result set consists of multiple columns, and all columns support only numeric values, then such a result set is said to be ‘non-info-based’.

A Stored Procedure on MS SQL that returns an ‘Info-based’ result set:

An info based test will typically return multiple rows of output, with each row representing the metrics for a particular info. A non-info based test, on the contrary, will always have a single row of output.

In case of a non-info-based test therefore:

The total number of measures for the test = The total number of columns returned by the query

In case of an info-based test:

The total number of measures for the test = (The total number of columns in the query output) - 1.

The first column of an info-based result set represents the name of the info.

Since the stored procedure in our example needs to return one set of measures for every processor supported by a given TargetHost, it should return an info-based result set. Given below is the stored procedure, avgCpuUtil, which has been created on the MS SQL server database for the purpose of our example:

CREATE PROCEDURE avgCpuUtil @host varchar(30)
   as
    SELECT ‘Processor_’+info, avg(cpu_util) Avg_cpu_util
        FROM systemtest
        WHERE trgt_host=@host
        GROUP BY info
        ORDER BY info

Note that the stored procedure takes the argument, @host. You can see that the value for this argument is matched with the value of the trgt_host column in the systemtest table. trgt_host is a column in the systemtest table, which holds the IP address of the monitored hosts. While configuring the AvgCpuUtilTest_ex using the eG administrative interface, you will be required to pass a value to the targethost parameter of the test. When the stored procedure executes, it assigns the targethost value to the @host argument, and then compares the @host value with the values in the trgt_host column. Once a match is found, the procedure retrieves the processor names and CPU usage statistics that correspond to that trgt_host from the systemtest table, computes the average CPU usage for every processor, and returns the resultant value to the alias, Avg_cpu_util - this is nothing but the measure that we had configured in Figure 11.

A Stored Procedure on MS SQL that returns a ‘Non-info-based’ result set:

Let us also see how a non-info-based stored procedure is to be constructed. When a stored procedure returns a result set comprising of multiple columns, all of which contain only numeric values, then this is a ‘non-info-based’ stored procedure. For example, assume that you need to create a stored procedure that computes the average CPU utilization of a host across processors (and not per processor). Such a stored procedure is ‘non-info-based’, and can be coded as follows:

CREATE PROCEDURE avgCpuUtil @host varchar(30)
   as
    SELECT avg(cpu_util) Avg_cpu_util
        FROM systemtest
        WHERE trgt_host=@host       

A Stored Procedure on Oracle:

The broad steps that you should follow for creating a stored procedure on Oracle are as follows:

  1. First, you have to create a package of type CURSOR in the Oracle database from which the metrics are to be extracted.
  2. Next, you should write a function that returns a cursor of that type.

For instance, to write a stored procedure that should return the average CPU usage of every processor on a specific host, you should follow the steps given below:

  1. Create a package named, say, cpuUtilAvg_pack of type cpuUtilAvg_cursor in the Oracle database from which the metrics are to be extracted

    CREATE OR REPLACE PACKAGE cpuUtilAvg_pack
    AS
    TYPE cpuUtilAvg_cursor IS REF CURSOR;
    END cpuUtilAvg_pack;

  2. Then, write a function that returns a cursor of type cpuUtilAvg_cursor

    CREATE OR REPLACE PROCEDURE cpuUtilAvg_procedure (host IN systemtest.trgt_host%TYPE, resultCursor OUT cpuUtilAvg_pack.cpuUtilAvg_cursor)
    AS
    BEGIN
      OPEN resultCursor
      FOR
        SELECT decode(info,’+’,’DEFAULT’,info) as info, avg(cpu_util) from systemtest WHERE trgt_host = host GROUP BY info ORDER BY info;
    END cpuUtilAvg_procedure;

The above lines of code create a stored procedure named cpuUtilAvg_procedure, which performs the following functions:

  • Queries the average CPU utilization of a given host
  • Groups the CPU usage value by processor
  • Returns the results to the cursor, <packagename>.<packagetype> - i.e., cpuUtilAvg_pack.cpuUtilAvg_cursor. 

If there is no error in the generation of the stored procedure, the eG Enterprise system prompts the user to specify the default threshold settings for the measurement made by the newly added test (see Figure 13).

Figure 13 : Specifying the threshold values of the Avg_cpu_util measure

You can even click on the Add Help button therein to create and upload Admin and Monitor help pages for the new test. To know how, refer to Adding a Custom Configuration Test.