9 Creating Alerts

Learn about creating alerts.

9.1 About Alerts

You should understand how alerts work in general and how to define useful alerts.

9.1.1 Overview

Alerts can be used for targets and third-party plug-ins.

You can create and configure alerts on events for targets, and for third-party plug-ins that have been developed using the Oracle Audit Vault and Database Firewall SDK. These events may be collected by the Audit Vault Agent or the Database Firewall. Alerts are independent of audit policies or firewall policies.

Alerts are rule-based. That is, if the rule definition is matched (for example, User A fails to log in to Client Host B after three tries), then an alert is raised. An alert can be applied to multiple targets, such as four Oracle databases. The alert rule can include more than one event and the event comes from different targets. For example, User A failed to log in to target X and User A also failed to log in to target Y.

You can specify an alert severity. Also, if a target is monitored by a Database Firewall, you can configure alerts based on audit records sent by the firewall, in addition to the alerts specified in the firewall policy.

When you configure an alert, you can set up an email to be automatically sent to a user, such as a security officer, or to a distribution list. You can also configure templates to be used for email alert notification.

Alerts are raised when the audit data reaches the Audit Vault Server, not when the event that raises the alert occurs. The time lag between when the event occurs and when the alert is raised depends on several factors, including how frequently the audit records are collected. The timestamp of an alert event indicates the time that the event occurred (for example, the time that User A tries to log in). The timestamp for the alert indicates when the alert was raised.

9.1.2 Defining Useful Alerts

A good way to define meaningful alerts is to first browse activity reports in Oracle Audit Vault and Database Firewall.

Activity reports contain a variety of audit and network event data, so browsing them can help you determine the key fields in audit records that are of special interest to you. These audit record fields are columns in the activity reports.

Looking at the report columns of interest, and the values in those columns, is a useful starting point for creating an alert that focuses on the audit events on which you want to be alerted. You can then create an alert with a condition (a rule) that defines the specific audit record field(s) and values that will trigger the alert.

For example, suppose you want to be alerted on schema changes to certain database objects. You can start by browsing the Database Schema activity report.

In this report, you can see the various database target objects, users, client program names, and other data associated with schema change audit events captured by Oracle Audit Vault and Database Firewall. From here, you can decide which target objects you want to alert on. You can then narrow down the alert to specific users, client programs, etc.

9.2 Creating Alerts and Writing Alert Conditions

Learn about creating alerts and writing alert conditions.

9.2.1 Creating or Modifying an Alert

You create custom alerts or use a predefinted alert.

When you create an alert in Oracle Audit Vault and Database Firewall, you define the conditions that will trigger the alert, and specify the type of notification that will be sent, and to whom. For example, you could create an alert that is raised each time User X tries to modify Table Y, which will notify administrator Z, using a specific email notification template. Oracle Audit Vault and Database Firewall has a preconfigured alert that is triggered based on alert settings in your Database Firewall policy. The alerts you create are for audit and other events not associated with Database Firewall.
  1. Log in to the Audit Vault Server console as an auditor.

  2. Click on Policies tab.

  3. From the left navigation menu, select Alert Polcies.

  4. To view or modify the definition for an existing alert, click its name in the Alert Name field.

  5. To create a new alert definition click Create.

  6. Enter the Alert Name.

  7. Specify the information in the following fields:

    • Type: Select a target type. For example, Oracle Database.

    • Severity: Select Warning or Critical.

    • Threshold (times): Enter the number of times the alert condition should be met before the alert is raised.

    • Duration (min): If you entered a threshold value that is more than 1, enter the length of time (in minutes) that this alert condition should be evaluated to meet that threshold value. For example if you enter a threshold of 3 and duration of 5, then the condition must be met 3 times in 5 minutes to raise an alert.

    • Group By (Field): Select a field from the list to group events by this column for this alert.

    • Description: Optionally, enter a description for this alert.

    • Condition: Enter a Boolean condition that must be met for this alert to be triggered.

      You can click any of the Condition - Available Fields listed on the right to enter them as part of the alert condition. These fields are the permissible audit or network event fields you can use to build your condition in the following format:

      :condition_field operator expression

      You can use any valid SQL WHERE clause with the available fields, making sure to include a colon (:) before that field. For example, your condition may be:

      upper(:EVENT_STATUS)='FAILURE'

  8. Optionally, in the Notification area, specify the following information:

    1. Template: Select a notification template to use for this alert. (To create alert templates.)

      See Creating Templates and Distribution Lists for Email Notifications for more detailed information.

    2. Distribution List: Select an email distribution list that will be notified about this alert.

    3. To: Enter email addresses, separated by commas, to receive notifications.

    4. Cc: Enter email addresses, separated by commas, to be copied on notifications.

    5. Click Add to List to record the email recipients that you entered in the To and Cc fields.

  9. Click Save.

    The new alert appears in the Alert Policies page.

You can monitor alert activity from the dashboard on the Audit Vault Server console Home page.

9.2.2 Writing Alert Conditions

Learn how to define alert conditions.

9.2.2.1 About Alert Conditions

Learn about alert conditions.

The Alert Condition is the where clause of a select statement. In the Condition field of the Create Alert page, you can construct a Boolean condition that evaluates audit events. When the Boolean condition evaluates to TRUE, then Oracle Audit Vault and Database Firewall raises the alert, and notifies any specified users. As a general guideline, try to keep your alert conditions simple. Overly complex conditions can slow the Audit Vault Server database performance.

9.2.2.2 Writing an Alert Condition

Learn how to write an alert condition.

Syntax of Alert Conditions

The syntax for an alert condition is:

:condition_field operator expression

For example:

:event_status='FAILURE' and upper(:event_name)=upper('LOGON')

An alert condition is a WHERE clause in a SELECT statement, with an added colon (:) before the fields. For example, the above condition looks like the WHERE clause in this SELECT statement:

SELECT user_name, event_status, event_name from avsys.event_log
   WHERE event_status='FAILURE' and upper(event_name)=upper('LOGON');

The WHERE clause above captures events in the avsys.event_log table where the event was LOGON and the event status was FAILURE. Converting this WHERE clause to an alert condition will cause that alert to be triggered whenever there are failed logons. You can specify in the alert how many failed logons within a specified period of time trigger the alert.

Rules for Writing Alert Conditions

Table 9-1 lists the rules for writing alert conditions and gives some examples.

Table 9-1 Rules for Writing Alert Conditions

Use the available audit record fields The Create Alert page has a list of fields you can copy and use to build the alert condition. See .

Use any legal SQL function

You can use any legal SQL function, including user-defined functions. However, you cannot use sub-query statements. For example, you can use:

  • upper()

  • lower()

  • to_char()

Use any legal SQL operator

For example, you can use:

  • not

  • like

  • <

  • >

  • in

  • and

  • null

When using operators, follow these guidelines:

  • Remember that Oracle Audit Vault and Database Firewall evaluates an alert condition for each incoming audit record.

  • You cannot use nested queries (for example, not in SELECT...) in the condition.

Use wildcards

You can use the following wildcards:

  • % (to match zero or more characters)

  • _ (to match exactly one character)

Group components of a condition

You can group components within the condition by using parentheses. For example:

(((A > B) and (B > C)) or C > D)

Example 1

You want to be alerted whenever there are three failed logon attempts on Oracle Database targets within a five-minute period.

To write a condition for this alert, you can copy EVENT_STATUS and EVENT_NAME from the available fields list, and use them to write this condition:

upper(:EVENT_STATUS)='FAILURE' and upper(:EVENT_NAME)='LOGON'

Tip: Set the threshold to 3 (3 times) and duration to 5 (less than 5 minutes) with this condition. You can look up audit event names and attributes in Oracle Database Audit Events through Active Directory Audit Events.

Example 2

You want to monitor application shared schema accounts that are being used outside the database. An example of this scenario is when the database user is APPS and the client identifier is set to NULL.

To write a condition for this alert, you can copy the EVENT_NAME and USER_NAME fields from the available fields list, and use them to write this condition:

:EVENT_NAME='LOGON' and :USER_NAME='apps' and :CLIENT_IP=NULL

This condition says, "Raise an alert if any ex-employee tries to log in to the database."

Tip: You can look up audit event names and attributes in Oracle Database Audit Events through Active Directory Audit Events.

Alert for Example 1 (mentioned above) in the Audit Vault Server Console

This alert says: "Alert me whenever there are three failed logon attempts on Oracle Database targets within a five-minute period."

The alert Condition uses two of the Condition - Available Fields on the right side of the Create Alert page.

If this alert is raised, its Severity will be set to Warning. An email will also be sent to the user avdf_auditor@samplecompany.com, using the Alert Notification Template.

In reports, instances of this alert will be grouped by client application ID.

Available Audit Record Fields for use in Alert Conditions

Table 9-2 describes the available audit record fields you can use in alert conditions.

Important: These fields must be preceded by a colon (:) when used in the condition (for example :USER_NAME).

Table 9-2 Available Fields for Alert Conditions

Condition Field Description

ACTION_TAKEN

(Firewall Alerts) Action taken by the Database Firewall, for example: BLOCK, WARN, or PASS

AV_TIME

The time Oracle Audit Vault and Database Firewall raised the alert

CLIENT_HOST_NAME

The host name of the client application that was the source of the event causing the alert

CLIENT_ID

The ID of the client application that was the source of the event causing the alert

CLIENT_IP

The IP address of the client application that was the source of the event causing the alert

CLUSTER_TYPE

(Firewall Alerts) The cluster type of the SQL statement causing the alert. Values may be:

Data Manipulation
Data Definition
Data Control
Procedural
Transaction
Composite
Composite with Transaction

COMMAND_CLASS

The Oracle Audit Vault and Database Firewall command class.

Tip: You can look up audit event names and attributes in Oracle Database Audit Events through Active Directory Audit Events.

ERROR_CODE

The target's error code

ERROR_MESSAGE

The target's error message

EVENT_NAME

The target's audit event name.

Tip: You can look up audit event names and attributes in Oracle Database Audit Events through Active Directory Audit Events.

EVENT_STATUS

Status of the event: Success or Failure

EVENT_TIME

The time that the event occurred

LOCATION

Describes where the audit trail is located. Valid values are:

Audit File
Audit Table
Transaction Log
Event Log
Syslog
Network
Custom

NETWORK_CONNECTION

Description of the connection between the target database and the database client, in the following format:

client_ip:client_port,database_ip:database_port

For example:

198.51.100.1:5760,203.0.113.1:1521

POLICY_NAME

The name of the Database Firewall policy or audit policy that generated this event.

For Oracle AVDF 20.3 and later: In case of audit data collected by the Agent, the policy name contains the audit policies that caused the current event.

REPOSITORY_NAME

The name of the Container Database

ROW_COUNT

The number of rows returned by a SELECT DML query.

Note: To fetch the row count, create a Database Object rule in a Database Firewall policy on the target. See Database Object Rule for more information.

OSUSER_NAME

Name of the target's OS user

SECURED_TARGET_CLASS

targets fall into these classes:

Database
OS
Directory Service
Filesystem

SECURED_TARGET_NAME

Name of the target in Oracle Audit Vault and Database Firewall.

TARGET_OBJECT

Name of the object on the target, for example, a table name, file name, or a directory name. Must be in upper case, for example, ALERT_TABLE.

TARGET_OWNER

Owner of the object on the target

TARGET_TYPE

The object type on the target, for example, TABLE, or DIRECTORY

TERMINAL

The Unix terminal that was the source of the event causing the alert (for example, /dev/1)

THREAT_SEVERITY

(Firewall Alerts) The threat severity of the SQL statement triggering the alert, as defined in a Database Firewall policy. Values may be: Minimal, Minor, Moderate, Major, or Critical.

USER_NAME

User name of the target user

AUDIT_TYPE

Oracle AVDF 20.3 and later

Audit types for Oracle Database target:

  • Standard
  • FineGrainedAudit
  • XS
  • Database Vault
  • Label Security
  • RMAN_AUDIT
  • Datapump
  • Direct path API

APPLICATION_CONTEXT

Oracle AVDF 20.3 and later

Application context information.

DATABASE_NAME

Oracle AVDF 20.4 and later

The name of the DB2 database that contains the audit records.

INSTANCE_NAME

Oracle AVDF 20.4 and later

The name of the instance which hosts the DB2 database.

RULE_NAME

Oracle AVDF 20.5 and later

The name of the rule defined by the user in Database Firewall policy.

9.2.3 Disabling, Enabling, or Deleting Alerts

Learn how to enable, disable, or delete alerts.

You can disable an alert while keeping the alert definition in case you wish to enable this alert again in the future.

To disable or enable alerts:

  1. Log into the Audit Vault Server console as an auditor.
  2. Click on Policies tab.
  3. From the left navigation menu, click Alert Policies. The alerts list is displayed on the main page.
  4. Select the check box(es) to the left of the Alert Name column for the specific alerts. Click the Disable, Enable, or Delete button to perform that action on all selected alerts.

9.3 Monitoring Alerts

Oracle AVDF raises an alert when data matches an alert rule condition in a single audit record, or matches multiple events with its duration and threshold setting.

Auditors can view recently raised alerts in the dashboard on the Audit Vault Server console's Home page. Alerts are grouped by the time that the alerts are raised, and by the severity level of the alert (warning or critical). Clicking on the circle marker available on the line chart will redirect you to Alert Reports under the Alerts tab.

You can also schedule alert reports from the Audit Vault Server Reports tab.

9.4 Responding to an Alert

After you have created alerts and when they are generated, you or other auditors can respond to them.

You can change the alert status (for example, closing it), or notify other users of the alert.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Click on Alerts tab.
    A table of alerts can be seen. The table contains information regarding:
    • Alert ID
    • Alert Status
    • Alert Policy
    • Target
    • User
    • Event
    • Object
    • Alert Severity
    • Event Time
  3. You can filter the list of visible alerts by clicking the Action drop-down at the top of the table and select Filter.
    1. Select the column to filter by from the Column drop-down.
    2. Select the an operator from the Operator drop-down.
    3. Enter in an appropriate value in the Expression field if applicable.
  4. Select the check box in the left column to select a specific alert and perform any of the following actions:
    1. Click the Notify button, to notify another auditor of the alert. In the Manual Alert Notification page, select the template type for the notification. Select a distribution list and/or enter email addresses in the To or Cc fields. Separate multiple email addresses with a comma. Click the Add to List button to compile the listing, and then click the Notify button to send the notification.
    2. From the Set Alert Status list, select , Open or Closedto set the alert status, and then click the Apply button. When an alert is first generated, it is set to New.
    3. Click the Alert ID of an alert to get additional details of the alert on the report.

9.5 Creating Custom Alert Status Values

You can create alert status values to assign to an alert during the lifetime of the alert.

Oracle Audit Vault and Database Firewall provides two status values: New and Closed prior to Oracle AVDF 20.8 and Open and Closed starting in Oracle AVDF 20.8. You can create additional ones to suit your needs, such as Pending.
  1. Log in to the Audit Vault Server console as an auditor.
  2. Click on Alerts tab.
  3. From the left navigation menu, click on Manage Alert Status.

    In this page there are two tabs: Custom Alert Status and Pre-configured Alert Status. From here you can edit or delete existing alert status values.

  4. To create a new alert status, click Create.
  5. In the Create Alert Status Value dialog, enter the following settings:
    • Status Value: Enter a name for the status value (for example, Pending).

    • Description: Optionally, enter a description for the status value.

  6. Click Save.

    The new alert status appears in the Manage Alert Status page.

9.6 Forwarding Alerts to Syslog

In addition to seeing alerts in reports, and receiving them in alert notifications, you can forward all alert messages to syslog.

As a prerequisite to forwarding alerts to syslog, the Oracle Audit Vault and Database Firewall administrator must configure syslog destinations in the Audit Vault Server, and select Alert as a syslog category. See the Oracle Audit Vault and Database Firewall Administrator's Guide for instructions.
  1. Log in to the Audit Vault Server console as a super auditor.
  2. Click on Policies tab.
  3. Click on Alert Policies tab in the left navigation menu.
  4. Click Forward Alerts to Syslog button. The button only appears if the Syslog connector is set up by the Oracle AVDF administrator.

    All defined alerts are forwarded to Syslog.

Example 9-1 Oracle Audit Vault and Database Firewall Syslog Alert Message Format

Oracle Audit Vault and Database Firewall alerts appear in syslog in a format similar to the following:

[AVDFAlert@111 name="alert_name" severity="alert_severity" 
url="auditor_console_URL_for_alert" time="alert_generated_time" target="secured_target" user="username" desc="alert_description"]

The user and target parameters may list zero or more users or targets related to this alert.

Example:

Apr 16 23:22:31 avs08002707d652 logger: [AVDFAlert@111 name="w_1" severity="Warning" url="https://192.0.2.10/console/f?p=7700..." time="2014-04-16T22:55:30.462332Z" target="cpc_itself" user="JDOE" desc=" "]