Configuring the Data Source in Database Policies

The source page of the database policy editor enables you to set up the connection to the database and to specify which database tables the policy accesses.

To access

• In the Operations Connector user interface, click in the toolbar. Then click Event > Database.

• In the Operations Connector user interface, click in the toolbar. Then click Metric > Database.

• In the Operations Connector user interface, click in the toolbar. Then click Generic output > Database.

Alternatively, double-click an existing policy to edit it.

Click Source to open the policy Source page.

Database drivers packages supplied with Operations Connector

Operations Connector 10.11 does not provide any production driver packages. You need to install a compatible JDBC database driver provided by your database vendor or a third party driver.

In previous releases, Operations Connector provided driver packages that could be used in a test environment. Starting with Operations Connector 10.00, the testing drivers are no longer supplied.

Understanding How Data From an SQL Query is Processed

When querying the database - for example with a combination of select and from SQL clauses - all the rows from the table defined in the clause are retrieved, unless you define a further clause that filters the data. For example, you may want to retrieve only entries that were added after a certain point in time and not all entries in the table. To do so, you can compare the results returned by the SQL clause against a data map that you can populate with an initial SQL statement or by setting up a session variable for each key.

When the initial SQL statement is executed, the data map is initialized with values. The values returned as last record from the execution of the statement are copied to the data map and returned. You can then compare the values in your SQL query against the values in the data map by referring to the keys (<$DATA:<key>>). This will be replaced with the current value of the <key> in the data map.

To make sure that the data map is not empty if the initial SQL statement does not return any values, you should set a session variable for each key. This value is then used to replace <$DATA:<key>> in the executable SQL statement.

Tasks

How to configure the prerequisites

• There are several key database driver requirements for using this policy.

  • You must install or copy a compatible JDBC database driver or database access API locally on the Operations Connector systems. Many database driver packages are available as compressed (zipped) archive files or .jar files. If the file is in zip format, unzip the contents. A recommended location for the downloaded driver is the directory the C:/Program Files/HP/HP BTO Software/java.

  • You must know the syntax for accessing the database driver. See the driver documentation for details.

    Examples of common database driver strings are:

    • com.microsoft.sqlserver.jdbc.SQLServerDriver The Microsoft SQL Server JDBC driver.

    • oracle.jdbc.OracleDriver The Oracle JDBC driver.

• You must know the syntax for the Database Connection string. The Database Connection string normally includes the class of driver you are using, some key name relating to the supplier of the driver software, followed by a combination of server, host, and port identifiers. For details, see the driver documentation.

  Example: Database Connection URLs for Microsoft SQL Server for this policy:

  • jdbc:sqlserver://<hostname>:1433;Database=<name>;
    
    where <hostname> is the name of the host where the database is running and <name> is the name of the database.

• The database you want to query must be running, have a database name defined, and have at least one named table created in the database. In some cases, the database management software needs to be configured to enable connections by using the middleware or database driver.

• You need a valid user name and password to access and perform a query on the database. In some cases, the machine and user account that Operations Connector is running on must be given permissions to access the database.

• You must know a valid SQL query string for the database instance and database tables in the database you want to query. Consult your database administrator to work out required queries to use.

• Use a database client to connect to the relevant software database. Identify which tables contain the required data (the software schema documentation may help you with this).

How to configure the database source

This task describes how configure the database source and how the policy queries the database.

1. Configure the connection to the database:

   1. In the Classpath field, type the location of the .jar file that is loaded.

      Example: C:/Program Files/HP/HP BTO Software/java/sqljdbc4.jar
      (Microsoft SQL Server installed in the default folder)

   2. In the JDBC Driver Class field, type the name of the driver used to connect to the database. Use the Fully Qualified Class Name of the JDBC driver you are using.

      Example: com.microsoft.sqlserver.jdbc.SQLServerDriver
      (Microsoft SQL Server Driver class)

   3. In the Connect string field, type the URL to the database connection (referred to as an Authentication string).

      Example: jdbc:sqlserver://system2.company.com:1433;Database=BSMEVENTS
      (Microsoft SQL Server Driver connection string)

   4. Enter the username and password.

   5. Under Polling interval, specify how often the policy queries the database. Use the spinboxes to specify increments of seconds, minutes, hours, and days.

   Make sure that you set this value to a minimum of 15 seconds to be able to save the policy.

   6. Optionally, enter any additional connection properties needed by the database to which you are connecting. See the database documentation for details.

2. Select the Collection tab and specify the SQL query:

   1. An SQL statement to query the data.

      Write a query that fits the database you are using and the type of data you are querying.

      For example, a basic query might look like this:

      "select {* | <column>[,<column>...] [,<column> ...]} from <table>[,<table>...] [where <SQL clause to define select criteria>"]

      select clause: enter * for all fields or a comma separated list of column names to be retrieved from the database.

      from clause: enter a table name or a comma separated list of tables from which the selected columns should be extracted.

      where clause: enables you to define the selection criteria. If you not define it, all the rows from the table defined in the from clause are retrieved.

      The queries are not limited to this example set and you can write more complex ones that suit your needs.

      Click to retrieve the specified table columns from the database. The results are displayed in the Sample Data tab of the policy.

      See the example at the end of this step to see how you can connect the SQL statement, the initial value statement, and the memento.

   2. Define initial values for keys by setting up the Session Variables.

      Click to add a new field. Type the name of the key and its initial value. Alternatively, you can drag and drop entries from the Initial value sample data tab.

      It is recommended that you set up a session variable for each key. If no records are found with the initial value statement, the value set up in the session variable is used to replace the key in the executable SQL statement. Without an initial value the SQL statement for the execute method is not valid.

      See the example at the end of this step to see how you can connect the SQL statement, the initial value statement, and the session variable.

   3. Enter the Initial value statement.

      An SQL statement that is executed in the init method. It can be used to initialize the data map with values. The values returned as last record from the execution of the statement are copied to the data map and returned.

      Click to retrieve the specified table columns from the database. The results are displayed in the Initial Value Sample Data tab of the policy.

   Example:

   The following example shows how you can set up a combination of an SQL statement, an initial value statement, and a session variable to query the table "dbo.ALL_EVENTS" that contains the columns "ID", "TITLE", and "TIME_RECEIVED", and to collect all new records after the activation of the policy.

   SQL statement:

   "select ID,TITLE,TIME_RECEIVED from dbo.ALL_EVENTS where TIME_RECEIVED > '<$DATA:TIME_RECEIVED>' order by TIME_RECEIVED;"

   <$DATA:TIME_RECEIVED> will be replaced with the current value of TIME_RECEIVED in the data map. Therefore this SQL statement will return all new records that are added to the table after the SQL init statement was called and set the value in the data map.

   Initial value statement: The following initial statement is used to fill the data map when the policy is activated:

   "select top 1 ID,TIME_RECEIVED,TITLE from dbo.ALL_EVENTS order by TIME_RECEIVED DESC;"

   This statement returns the newest record from the table. The values of the three columns are stored in the data map and can later be accessed in the SQL statement.

   Session variables: TIME_RECEIVED "2014-09-09 00:00:00.000"

   This is the initial value for the key TIME_RECEIVED. If no records are found with the initial SQL statement, this value is used to replace <$DATA:TIME_RECEIVED> in the executable SQL statement.

3. Optionally, select the Internals tab and modify the default settings for the:

   1. Fetch Size - the maximum number of entries the policy sends at once.
   2. Result size - the maximum number of entries the policy retrieves from the database in each run.

   By adjusting the fetch and result sizes you can balance the loads on the database and the Operations Connector system.

Related tasks

• How to Collect Event Data from Databases

• How to Collect Metrics Data from Databases

• How to Collect Generic Output Data from Databases

UI Descriptions

Related topics

How to configure the database source