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

Database Enrichment Examples

The following examples show how you can use the enrich event action to enrich events with information from an external database when search patterns match:

Note: For detailed end-to-end scenarios that use the database enrichment action, see Event Management Scenarios.

Example: Enrich events with database contact information

This example enriches events with contact information stored in an external database. This contact information could be useful for alert assignment and problem resolution. You could use the contact information to create alert queues based on the assigned technician or to automate emailing the assigned technician as part of an escalation policy.

Note: This example uses sample database server information that you can replace.

• Select JDBC on the Enrichment Configuration page, and enter database connection information according to the database type, database server, and database table. This example uses the following:
  • The Microsoft SQL Server conventions described in JDBC Connection Examples and accessible from the MS SQL entry in the Templates drop-down list.
  • The database server dbserver1 in the Connection string
  • The database name ResourceCatalog in the Connection string
  • The database table Contacts in the Table or View field
• Do the following on the Enrichment Policy page:
  • Select HostName from the right-click menu in the Input Parameter column and enter ${pattern1.AlertedMdrProdInstance} in the corresponding Assigned Value cell.

    This parameter configuration queries the HostName column of the database based on the value of the AlertedMdrProdInstance property.

  • Select Name from the right-click menu in the Assigned Value cell corresponding to the User Attribute 1 Event Property cell.
  • Select Email from the right-click menu in the Assigned Value cell corresponding to the User Attribute 2 Event Property cell.

  This enrichment queries the Contacts table of the ResourceCatalog database using a SQL statement similar to the following:

  SELECT Name, Email WHERE HostName=${pattern1.AlertedMdrProdInstance}
  

  The enrichment uses the AlertedMdrProdInstance value of each event and searches for a match in the HostName column of the database. If there is no match, the enrichment does not occur. If there is a match, the Name and Email column values are returned from the matching row and assigned to the User Attribute 1 and User Attribute 2 properties in the enriched event.

  For example, consider an event with the AlertedMdrProdInstance value of server1. The server1 value matches a HostName database column value. The Name and Email values in the matching row are Dave and dave@ca.com, and these values appear in the enriched event for User Attribute 1 and User Attribute 2.

• Deploy the policy on the Mid-tier connector to include events from all connectors in the policy.

Example: Enrich events with maintenance schedule information

This example enriches events with maintenance information stored in an external database. CA SOI automatically synchronizes maintenance information from several connectors. However, for connectors that do not synchronize maintenance, you could use an enrichment to pull the maintenance status and schedule from the domain manager database so that you can determine whether maintenance is the cause of an alert and enter a corresponding maintenance schedule for the CI in CA SOI.

Note: This example uses sample database server information that you can replace.

• Select JDBC on the Enrichment Configuration page, and enter database connection information according to the database type, database server, and database table. This example uses the following:
  • The Oracle conventions described in JDBC Connection Examples and accessible from the Oracle entry in the Templates drop-down list.
  • The database server dbserver2 in the Connection string
  • The database name ProductDB in the Connection string
  • The database table Maintenance in the Table or View field
• Do the following on the Enrichment Policy page:
  • Select HostName from the right-click menu in the Input Parameter column and enter ${pattern1.AlertedMdrProdInstance} in the corresponding Assigned Value cell.

    This parameter configuration queries the HostName column of the database based on the value of the AlertedMdrProdInstance property.

  • Select Status from the right-click menu in the Assigned Value cell corresponding to the User Attribute 1 Event Property cell.
  • Enter fx:xsdateTime(${StartTime}), using the right-click menu to select the function and embedded column value, in the Assigned Value cell corresponding to the User Attribute 2 Event Property cell.
  • Enter fx:xsdateTime(${EndTime}), using the right-click menu to select the function and embedded column value, in the Assigned Value cell corresponding to the User Attribute 3 Event Property cell.
  • Enter fx:fdqn(${Backup}), using the right-click menu to select the function and embedded column value, in the Assigned Value cell corresponding to the User Attribute 4 Event Property cell.

  This enrichment queries the Maintenance table of the ProductDB database using a SQL statement similar to the following:

  SELECT Status, StartTime, EndTime, Backup WHERE HostName=${pattern1.AlertedMdrProdInstance}
  

  The enrichment uses the AlertedMdrProdInstance value of each event and searches for a match in the HostName column of the database. If there is no match, the enrichment does not occur. If there is a match, the Status, StartTime, EndTime, and Backup column values are returned from the matching row and assigned to the User Attribute 1-4 properties in the enriched event.

  For example, consider an event with the AlertedMdrProdInstance value of server5. The server5 value matches a Host Name database column value. The values in the matching row are as follows:

  • Status: Maintenance
  • StartTime: 05 09 2011 5:00:00
  • EndTime: 05 12 2011 9:00:00
  • Backup: 12.543.34.58

  The Maintenance value appears in the User Attribute 1 property of the enriched event, and you can set the maintenance status of the CI accordingly in CA SOI. The StartTime and EndTime column values are converted to dateTime strings and appear in the User Attribute 2 and 3 properties of the enriched event. You could use these values to create a corresponding maintenance schedule for the CI in CA SOI. The IP address in the Backup column appears in the User Attribute 4 property of the enriched event converted to a fully qualified domain name, so that you can be aware of the CI performing the same function while the primary CI is in maintenance.

• Deploy the policy on the Mid-tier connector.