Create a Database Enrichment Action

Working with Event Policies and Actions › Create an Event Policy with an Enrichment Action › Create a Database Enrichment Action

Create a Database Enrichment Action

Create an event policy with a database enrichment action to query a database based on specified event properties and add returned information from the database to specific areas in the event. The database must be able to use a JDBC connection.

For example, you could query an internal database of contacts for all resources in your enterprise, and add the necessary contact to the event to accelerate alert assignment and resolution.

Database enrichments require the following information:

Connection information for the database
Match criteria that pinpoints a database row from which to extract the enrichment value based on event properties
The database column value to use for the enrichment output value

Deploy database enrichments on the Mid-tier connector only. CA Catalyst connectors do not support direct database enrichment policy deployment. Deploying them through the Mid-tier connector enriches CA Catalyst connector events without deploying directly on the connectors.

Follow these steps:

Create an event policy based on a search pattern, and select Enrich Event as the action type.
The Enrichment Configuration page opens.
Select JDBC in the Type drop-down list.
Fields appear for entering JDBC connection information.
(Optional) Select the appropriate database type in the Templates drop-down list.
Template connection information for the database type appears in the fields. Edit the template text with database-specific information, such as full path information, database server, and database name.
Enter information or edit template text in the following fields:
Note: The Test button does not work when using the SQL Server JDBC driver (sqljdbc.jar) for SQL Server connection, even though the enrichment does work with the SQL Server JDBC driver. Use a JTDS driver instead for SQL Server connections to enable the Test button.

Class Path

Defines the path and file name of the JDBC driver.

Example: C:\Program Files\Oracle\ojdbc6.jar

Class Name

Defines the JDBC driver class name of the database to use for the enrichment.

Example: oracle.jdbc.driver.OracleDriver

Connection

Defines the JDBC connection string of the database to use for the enrichment. Do not include credentials in the connection string; use the User and Password fields instead so that the password is encrypted.

Example: jdbc:oracle:thin:@server01:1521:MyDB

User

Defines the database user name.

Password

Defines the password for the specified database user name.

Table or View

Defines the database table from which to extract information for the enrichment. This field is case-sensitive and must exactly match the table name in the database.

Note: The text at the bottom of the page indicates if any required information is missing. The examples in this step are for an Oracle database. For connection examples for other database types, see JDBC Connection Examples.
(Optional) Click Test.
A confirmation dialog opens.
(Optional) Click Yes.
The database connection is verified. The Configuration Test Result dialog indicates whether the connection was successful. If an error occurred the displayed error message attempts to isolate the reason for the problem (for example, if the database table does not exist).

Note: If you have to change this information after deploying the policy, restart the CA SAM Integration Services service on the system to ensure that the change takes effect immediately. For information about how to configure enrichment value caching, see Configure Enrichment Cache Timeout.
Click Next.
The Enrichment Policy page opens. Right-click each column on this page for additional help information.
Enter the following in the Parameter Configuration table to construct the WHERE clause of a SQL query that determines how the input parameters to the enrichment process are assigned according to database column values and event properties:
Input Parameter

Defines the database column on which to search for the appropriate input value. Right-click in a cell to select from the available columns in the defined database table.

Note: If the right-click menu does not appear, there could be problems with the database connection. Return to the Enrichment Configuration page and test the database connection before proceeding. If you changed the table name and returned to this page, click on a different cell before right-clicking to avoid seeing columns from the previously entered database table.

Assigned Value
Defines the event property value to use to query the database for a value that matches the specified column in corresponding Input Parameter cell. Use the right-click menu to assign the value of a property from any matching pattern. For example, you can search a database column named HostName based on the value of the MdrProdInstance property. The search value for each database column can take any of the following forms:
- A full event property
- Multiple combined event properties
- Part of an event property
- Modified event properties
Use the right-click menu to add provided functions to perform common data conversions on the search value to use in the database query.

Note: Querying a database that uses fixed columns (which often applies with Oracle databases) may require you to pad the assigned value. Use a SQL function such as Rpad to add the additional spaces to ensure that the queried value is found. For example, you would need to enter the assigned value as follows if the column on which you want to match has a fixed width of 64 characters: rpad(${AlertedMdrProdInstance},64).
The Preview cell displays the result of the entered value based on the selected event in the Event Log table. You must run an event search before creating the policy to get its results in the Event Log table for previewing enrichment values based on existing event content.

You can include multiple columns in the Parameter Configuration table to use in the WHERE clause. If no match occurs for an event, the enrichment does not occur for that event. For example, if you want to enrich when the value of a HostName column matches that of the event's MdrProdInstance value, no enrichment occurs when no match is found.
Enter the following in the Enrichment Property Assignment table to specify how enrichment output values are assigned to event properties, and click Next:

Assigned Value

Defines the database column values to assign to the event properties in the Event Property column. This value completes the database query started in the Parameter Configuration pane. It is the SELECT statement that uses the WHERE clause constructed in the Parameter Configuration pane to select the specified column value to use for the enrichment output from the appropriate row.

Right-click in a cell to select from the available columns in the defined database table. If you enter columns manually, references to database columns must be in the following format: ${columnname}. For example, ${hostname} uses the returned value from the hostname database column for the enrichment. Any values entered without this format appear directly in the event as written. You can add enrichments to as many event properties as necessary.

Note: You can change the names of the User Attribute properties if you want them to accurately represent the enrichment properties that you assign to them. However, these properties appear under their original names in the Event Policy dialog, even if you renamed them. Assigning values to these original names properly displays the values under the renamed properties in the Operations Console.

The column-based value can be a single column value, multiple values, or a modified column value. Use the right-click menu to add provided functions to perform common data conversions on the enrichment value before assigning it to the specified property.

Note: Only the properties that support enrichment value assignment appear in the Event Property column.

The Select Data Sources page opens.
Save or deploy the policy.