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. Using the Unified Assurance UI, new fields can be added, and existing fields can be cloned and then have their settings changed. Changes can also be made to the index type of some of the fields that are provided out-of-the-box.

Note:

The out-of-the-box fields can not be deleted. Only custom added fields can be deleted.

After changes are done, an application must be run that will actually apply the changes to the database, then some additional changes within the Unified Assurance system may be needed.

After fields have been added to the database, the event 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.

Deduplication settings can be changed 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.

Dependencies

Adding New Fields and making other changes

Best Practices

Steps

Stop the Event applications and MySQL Replication Data Importer

  1. Go to the Services UI:

    Configuration -> Broker Control -> Services

  2. Find and select any of the following services that are running, then click on the "Stop" button.

    • Any event-based applications, such as the Event Syslog Aggregator, etc.

    • Any thresholding-based applications, such as the Metric Standard Thresholding Engine, etc.

    • Stop the MySQL Replication Data Importer service(s).

  3. Go to the microservices Installed UI:

    Configuration -> Microservices -> Installed

  4. Stop any event-based microservices, such as the Event Sink, etc.

Use the UI to prepare changes to the table

  1. Open the "Custom Event Fields" UI:

    Configuration -> Events -> Custom Event Fields

  2. Use the UI to add a new field, make changes to an existing field, or delete a custom field.

  3. Make additional changes as needed.

Apply the changes to the database

  1. Log in to the command line of the database server.

  2. Change to the root user.

  3. The ApplyEventSchema application is located in the $A1BASEDIR/bin/ directory. Go to the directory:

    cd $A1BASEDIR/bin/

  4. Run the ApplyEventSchema to apply the changes.

    ./ApplyEventSchema

    Note:

    • If using multiple event shards in your environment, one of the following must be done.

      • Run the application on one of the database servers passing the --EventShard 0 flag:

        $A1BASEDIR/bin/ApplyEventSchema --EventShard 0

      • Run the application on the primary instance of the database servers passing the --EventShard flag with the correct value:

        $A1BASEDIR/bin/ApplyEventSchema --EventShard N

    • The --Dry-Run option can be used to log the queries that will be executed to change the database:

      $A1BASEDIR/bin/ApplyEventSchema --Dry-Run

      Then look at the log file to see the queries that will be executed:

      lnav $A1BASEDIR/logs/ApplyEventSchema.log

Start the MySQL Replication Data Importer

  1. Go to the Services UI:

    Configuration -> Broker Control -> Services

  2. Find and select the MySQL Replication Data Importer service(s).

  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 were previously running.

  3. Click on the "Start" button.

Start the Event Microservices

  1. Go to the microservices Helmcharts UI:

    Configuration -> Microservices -> Helmcharts

  2. Select any event-based microservice(s) that were previously running.

  3. Click on the "Deploy" button.

  4. Fill in the needed settings, then 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, by manually updating an event through the UI, or other steps as well.

  1. Go to the Elasticsearch Management UI:

    Analytics -> Events -> Administration -> 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] [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,GeoPath,GeoLocation,IPAddress,LastChanged,LastReported,Location,Method,Node,OrigSeverity,OwnerName,RootCauseFlag,RootCauseID,Score,Service,ServiceImpact,Severity,SubDeviceType,SubMethod,SubNode,Summary,TicketFlag,TicketID,ZoneID,RootCauseKey,NewField1)
             VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,ST_GeomFromGeoJson(?,1,4326),ST_GeomFromGeoJson(?,1,4326),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
                 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] [BINARY NAME]([PROCESS ID])<-> [INFO] Build Generic FieldSet as:
EventKey,EventCategory,EventType,Ack,Action,Actor,Count,Customer,Department,Details,DeviceType,Duration,EscalationFlag,ExpireTime,FirstReported,GeoPath,GeoLocation,IPAddress,LastChanged,LastReported,Location,Method,Node,OrigSeverity,OwnerName,RootCauseFlag,RootCauseID,Score,Service,ServiceImpact,Severity,SubDeviceType,SubMethod,SubNode,Summary,TicketFlag,TicketID,ZoneID,RootCauseKey,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] [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,GeoPath,GeoLocation,IPAddress,LastChanged,LastReported,Location,Method,Node,OrigSeverity,OwnerName,RootCauseFlag,RootCauseID,Score,Service,ServiceImpact,Severity,SubDeviceType,SubMethod,SubNode,Summary,TicketFlag,TicketID,ZoneID,RootCauseKey,NewField1)
     VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,ST_GeomFromGeoJson(?,1,4326),ST_GeomFromGeoJson(?,1,4326),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
         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] [BINARY NAME]([PROCESS ID])<-> [INFO] Build Generic FieldSet as:
EventKey,EventCategory,EventType,Ack,Action,Actor,Count,Customer,Department,Details,DeviceType,Duration,EscalationFlag,ExpireTime,FirstReported,GeoPath,GeoLocation,IPAddress,LastChanged,LastReported,Location,Method,Node,OrigSeverity,OwnerName,RootCauseFlag,RootCauseID,Score,Service,ServiceImpact,Severity,SubDeviceType,SubMethod,SubNode,Summary,TicketFlag,TicketID,ZoneID,RootCauseKey,NewField1