Adding Custom Fields to Events DB

It is a common practice to add custom fields to the Events table, or to change the fields that are updated when a duplicate event is received. You can add new fields by executing basic MySQL commands and making some changes within the Unified Assurance system.

After you have added fields to the database, the applications will use built-in functionality on startup or reload to read in all of the database fields and create a default insert statement. This can be changed if different functionality is required, or if custom deduplication settings are required.

You can change the deduplication settings by creating two additional configuration files. Applications configured to use the new files will process the new settings instead of using the default settings. While these changes are relatively easy to make, they can cause issues if done incorrectly.

Note:

The following example is for reference only. Changes will need to be made to the SQL commands used below, so previous MySQL knowledge is highly recommended.

Dependencies

Adding New Fields

Best Practices

Steps

Stop the Event Services and MySQL Importer

  1. Go to the Services UI:

    Configuration -> Broker Control -> Services

  2. Find and select any running event based service(s).

  3. Click on the "Stop" button.

  4. Find and select the MySQL Replication Data Importer service.

  5. Click on the "Stop" button.

Modifying the Real-time Events Table

  1. Start a command line session to the server with the real-time database.

  2. Login to the database using the Unified Assurance shortcut:

    a1dbroot

    Note:

    If a "command not found" error is returned, the following can be run to load the Unified Assurance shortcuts:

    source $A1BASEDIR/.bashrc

    Next, run "a1dbroot" again.

  3. Switch to the Event database:

    use Event

  4. Run an alter command against the Events table to add the additional fields required. This is relatively basic, but do NOT do this if unfamiliar with MySQL:

    ALTER TABLE Event.Events ADD [New_Field_Name] [New_Field_Type] [Additional_Field_Options];

    Example:

    ALTER TABLE Event.Events ADD NewField1 VARCHAR(255);

    The above will create a new field called NewField1 as MySQL data type VARCHAR with a maximum length of 255 characters. The new field will be placed after the last column.

    The above is the only direct change needed to the MySQL database, but would need to be repeated for each additional field.

Start the MySQL Importer

  1. Go to the Services UI:

    Configuration -> Broker Control -> Services

  2. Find and select the MySQL Replication Data Importer service.

  3. Click on the "Start" button.

Update Rules Files (Optional)

  1. Go to the Rules UI:

    Configuration -> Rules

  2. Update the relevant rules files for applications to insert or update data in the real-time database.

Start the Event Services

  1. Go to the Services UI:

    Configuration -> Broker Control -> Services

  2. Find and select any event based service(s) that was previously running.

  3. Click on the "Start" button.

Recreate the Kibana Index

Note:

This step must be done after data has been inserted into the newly added fields. Data can be inserted into the fields using rules, manually through the UI, or other steps as well.

  1. Go to the Elasticsearch Management UI:

    Analytics -> Events -> Management

  2. In the Kibana section, click on the "Index Patterns" link.

  3. With the eventanalytics-* option selected, click on the refresh icon in the upper right.

Functionality Verification

To verify the new field (or fields) are available for use, starting or reloading an application should show the new fields as being available for use during the Build Generic Insert and Build Generic FieldSet logging:

[DATE TIME] [INFO] [BINARY NAME]([PROCESS ID]) [INFO] Build Generic Insert as:

        INSERT INTO Events (EventKey,EventCategory,EventType,Ack,Action,Actor,Count,Customer,Department,Details,DeviceType,Duration,EscalationFlag,ExpireTime,FirstReported,IPAddress,LastChanged,LastReported,Location,Method,Node,OrigSeverity,OwnerName,RootCauseFlag,RootCauseID,Score,Service,ServiceImpact,Severity,SubDeviceType,SubMethod,SubNode,Summary,TicketFlag,TicketID,ZoneID,NewField1)
             VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                 ON DUPLICATE KEY
             UPDATE Count         = Count + 1,
                    Duration      = VALUES(LastReported) - FirstReported,
                    EventCategory = VALUES(EventCategory),
                    LastChanged   = VALUES(LastChanged),
                    LastReported  = VALUES(LastReported),
                    Severity      = VALUES(Severity),
                    Summary       = VALUES(Summary)

[DATE TIME] [INFO] [BINARY NAME]([PROCESS ID]) [INFO] Build Generic FieldSet as:
EventKey,EventCategory,EventType,Ack,Action,Actor,Count,Customer,Department,Details,DeviceType,Duration,EscalationFlag,ExpireTime,FirstReported,IPAddress,LastChanged,LastReported,Location,Method,Node,OrigSeverity,OwnerName,RootCauseFlag,RootCauseID,Score,Service,ServiceImpact,Severity,SubDeviceType,SubMethod,SubNode,Summary,TicketFlag,TicketID,ZoneID,NewField1

Custom Deduplication

Best Practices

Steps

Updating Custom Fields on Deduplication

To get Unified Assurance to perform a custom action when a duplicate event is received, two additional files need to be created/updated. Refer to the Best Practices section for where the files should be placed.

Application Configuration Changes

The next step is to modify the application configuration settings in the UI to allow the applications to use the custom files:

  1. Go to the Services or Jobs UI:

    Configuration -> Broker Control -> Services

    Configuration -> Broker Control -> Jobs

  2. Find and select the application that will be using the new configuration files.

  3. In the Configuration section, find and update the FieldSetFile and InsertSQLFile options. (If they are not in the list, add the options.)

    • The FieldSetFile should be set to collection/event/common/FieldSetFile.sql (or the correct path).

    • The InsertSQLFile should be set to collection/event/common/InsertSQLFile.sql (or the correct path).

  4. Once these have been added, the applications must be restarted for the changes to take effect.

Functionality Verification

To verify the new fields are available for deduplication, starting or reloading an application should show the new fields during the Build Generic Insert and Build Generic FieldSet logging:

[DATE TIME] [INFO] [BINARY NAME]([PROCESS ID]) [INFO] Build Generic Insert as:
 INSERT INTO Events (EventKey, EventCategory, EventType, Ack, Action, Actor, Count, Customer, Department, Details, DeviceType, Duration, EscalationFlag, ExpireTime, FirstReported, IPAddress, LastChanged, LastReported, Location, Method, Node, OrigSeverity, OwnerName, RootCauseFlag, RootCauseID, Score, Service, ServiceImpact, Severity, SubDeviceType, SubMethod, SubNode, Summary, TicketFlag, TicketID, ZoneID, NewField1)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY
      UPDATE Count         = Count + 1,
             Duration      = VALUES(LastReported) - FirstReported,
             EventCategory = VALUES(EventCategory),
             LastChanged   = VALUES(LastChanged),
             LastReported  = VALUES(LastReported),
             Severity      = VALUES(Severity),
             Summary       = VALUES(Summary),
             NewField1     = VALUES(NewField1)

[DATE TIME] [INFO] [BINARY NAME]([PROCESS ID]) [INFO] Build Generic FieldSet as:
EventKey,EventCategory,EventType,Ack,Action,Actor,Count,Customer,Department,Details,DeviceType,Duration,EscalationFlag,ExpireTime,FirstReported,IPAddress,LastChanged,LastReported,Location,Method,Node,OrigSeverity,OwnerName,RootCauseFlag,RootCauseID,Score,Service,ServiceImpact,Severity,SubDeviceType,SubMethod,SubNode,Summary,TicketFlag,TicketID,ZoneID,NewField1