Adding Custom Fields to the Event Database

Using the Unified Assurance UI, you can add new custom fields to the event database, clone existing fields and then change their settings, or delete custom fields you have added. You can also make changes to the index type of some of the default fields. You cannot delete the default fields.

The event database is a MySQL database internal to Unified Assurance only. It is not intended to integrate directly with external tools or clients. Do not attempt to make changes in the event database through the command line, third party tools, or other methods than those described here. You can only use the Unified Assurance UI, application rules, and REST API to access the event database and make changes.

At a high level, making changes involves the following:

Stopping services.
Preparing changes in the UI.
Applying the changes to the database.
Optionally, updating rules files.
Restarting services, recreating indices.
Verifying functionality.

See Adding Custom Fields for details.

When you restart applications after adding custom fields to the database, the applications use read in the database fields and create a default insert statement. You can customize this statement if you need different functionality or custom deduplication settings. See Custom Deduplication for details.

Note:

The examples in this document are for reference only.

Adding Custom Fields

This section describes the tasks involved in adding custom fields. You perform the tasks using the Unified Assurance user interface (UI) and the command line.

Dependencies for Adding Custom Fields

A working aggregator/collector.
A list of additional fields names and field types/lengths.

Best Practices for Adding Custom Fields

When adding custom fields and making other changes, keep the following best practices in mind:

During initial deployment, ensure the application configuration has the LogLevel set to DEBUG. After the functionality has been verified, this can be changed to reduce log data.
Verify everything is working by checking that the events are inserting correctly, and no errors are seen in the application logs.
If a custom field is added to store a date time type of value (similar to the default FirstReported and LastReported fields), the data type should be DECIMAL(13,3) UNSIGNED instead of one of the MySQL date types. This should be used to store an epoch time instead of a hard-coded datetime value. This will then provide a consistent view of dates and times throughout the event list and other views.
Once a new field has been added, the field name cannot be modified. A specific process must be followed to handle renaming a field:
1. Follow Task 1 below.
2. Follow Task 2 below to create the new field.
3. Follow Task 3 below to apply the changes to the table.
4. Use the ApplyEventSchema application to move the data, passing in the old field name and new field name:
```
$A1BASEDIR/bin/ApplyEventSchema move-data <OldFieldName> <NewFieldName>
```
5. Follow Task 2 below to delete the old field.
6. Follow Task 3 below to apply the changes to the table.
7. Follow the remaining steps in the sections below.

Task 1: Stop Event Services and Microservices

In a browser, sign in to the Unified Assurance UI.
From the Configuration menu, select Broker Control, then Services.
Select any of the following services that are running and click Stop:
- Any event-based applications, such as the Event Syslog Aggregator.
- Any thresholding-based applications, such as the Metric Standard Thresholding Engine.
- MySQL Replication Data Importer services
From the Configuration menu, select Microservices, then Installed.
For any event-based microservices, such as Event Sink, click Delete.

Task 2: Use the UI to Prepare Changes to the Table

In a browser, sign in to the Unified Assurance UI.
From the Configuration menu, select Events, then Custom Event Fields.
Add a new field, make changes to an existing field, delete a custom field, and make additional changes as needed.

Task 3: Apply the Changes to the Database

Log in to the command line of the database server.
Change to the assure1 user.
Go to the following directory:
```
cd $A1BASEDIR/bin/
```
Optionally, run the ApplyEventSchema application with the --Dry-Run option for a preview of changes:
1. Log the queries that will be run to change the database:
```
./ApplyEventSchema --Dry-Run
```
2. Review the queries in the log file:
```
lnav logs/ApplyEventSchema.log
```
Run ApplyEventSchema:
- For non-sharded environments, run the following command:
```
./ApplyEventSchema
```
- For environments with multiple event shards, do one of the following:
- On any of the database servers, run ApplyEventSchema with the --EventShard 0 flag:
```
./ApplyEventSchema --EventShard 0
```
- On the primary instance of the database server, run ApplyEventSchema with the -EventShard flag, replacing <N> with the correct Event Shard ID:
```
./ApplyEventSchema --EventShard <N>
```

Task 4: Start the MySQL Replication Data Importer

In a browser, sign in to the Unified Assurance UI.
From the Configuration menu, select Broker Control, then Services.
Find and select any MySQL Replication Data Importer services.
Click Start.

Task 5: (Optional) Update Rules Files

In a browser, sign in to the Unified Assurance UI.
From the Configuration menu, Rules.
Update the relevant rules files for applications to insert or update data in the real-time database. See Custom Deduplication for examples of updating insert statements and deduplication settings in rules files.

Task 6: Start the Event Services and Microservices

In a browser, sign in to the Unified Assurance UI.
From the Configuration menu, select Broker Control, then Services.
Select any event-based services that you stopped in Task 1.
Click Start.
From the Configuration menu, select Microservices, then Installed.
Repeat the following for any event-based microservices that you stopped in Task 1:
1. Select the microservice.
2. Click Deploy.
3. Select a cluster and namespace, edit any default configurations, optionally update the release name, and click Start.

Task 7: Recreate the Kibana Index

Note:

Perform this task 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.

In a browser, sign in to the Unified Assurance UI.
From the Analytics menu, select Events, then Administration, then Management.
In the menu, under Kibana, select Index Patterns.
Select eventanalytics-*.
Click the Refresh button in the upper right.

Task 8: Verifying Functionality

To verify the new field (or fields) are available for use, start or reload an event-based application. Build Generic Insert and Build Generic FieldSet logging should show the new fields as being available for use.

For example:

[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

This section details the tasks involved in implementing custom deduplication so that Unified Assurance performs a custom action when a duplicate event is received. While these changes are relatively easy to make, they can cause issues if done incorrectly.

Best Practices for Custom Deduplication

During initial deployment, ensure the application configuration has the log level set to DEBUG. After the functionality has been verified, you can change this to INFO to reduce log data.
Verify everything is working by checking that the events are inserting and updating correctly and that no errors are seen in the application logs.
If errors are seen when the applications are trying to insert events, the most likely problem is one of the following:
- The application was not restarted to make it read in the custom files with the additional advanced configuration options.
- The .sql files have an error in them. Verify they match the new database field names exactly.
For ease of management, the configuration files needed for this process should be located in the common folder of the rules repository. Files in this folder are available to all servers without needing to manually manage individual files. The procedures in this section use files named InsertSQLFile.sql and FieldSetFile.sql, though you can optionally use custom names.

Task 1: Adding Custom Deduplication Files

To implement custom deduplication:

Create a InsertSQLFile.sql file with the following content:

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>)

This statement controls which fields are inserted when an event is initially received and which fields are updated when a duplicate event is received.

Replace <NewField1> and <?> and customize the statement as follows:
- In the INSERT INTO list of columns, replace <NewField1> with the new field name and add additional comma-separated fields to the list.
- In the VALUES list, replace <?> with a single question mark (?), and add additional comma-separated question marks for each additional field that is being added to the list.
- In the ON DUPLICATE KEY list, replace <NewField1> with the new field name. You can also add other MySQL functionality here.
Save the file.

Create a FieldSetFile.sql file with the following content:

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>

This information determines what fields are replacing the SQL placeholders (question marks) in the statement in InsertSQLFile.sql.

Replace <NewField1> with the new field name, and add each additional field to the list before saving the file.
Save the file.
Add both files to the common folder of the rules repository:
1. In a browser, sign in to the Unified Assurance UI.
2. From the Configuration menu, select Rules.
3. Expand the following folder path:
  
  Core Rules (rules)/Default read-write branch (default)/collection/event
4. Select the common folder.
5. Click Upload.
6. In FileName, enter InsertSQLFile, or your custom file name.
7. Click Browse and select your local InsertSQLFile.sql file.
8. Click Submit.
9. Click Upload.
10. In FileName, enter FieldSetFile, or your custom file name.
11. Click Browse and select your custom FieldSetFile.sql file.
12. Click Submit.

Task 2: Updating Application Configuration Settings

Repeat this procedure for any application that should use the custom files:

In a browser, log in to the Unified Assurance UI.
From the Configuration menu, select Broker Control, then either Services or Jobs.
Select the application.
In the Configuration section:
- Set InsertSQLFile to collection/event/common/InsertSQLFile.sql, or the custom location and name of your InsertSQLFile.
- Set FieldSetFile to collection/event/common/FieldSetFile.sql or the custom location and name of your FieldSetFile.
If these fields are not in the list, add them.
Restart the application.

Task 3: Verifying Custom Deduplication Functionality

To verify that the new fields are available for deduplication, start or reload an application. Build Generic Insert and Build Generic FieldSet logging should show the new fields.

For example:

[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

Title and Copyright Information

Implementation Guide

G10620-01