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 MariaDB commands and making some changes within the Assure1 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 MariaDB knowledge is highly recommended.

Dependencies¶

Have a working aggregator/collector.
List of additional fields names and field types/lengths.

Adding New Fields¶

Best Practices¶

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 MariaDB date types. This should be used to store an epoch time instead of a hard-coded date/time value. This will then provide a consistent view of dates and times throughout the event list and other views.

Steps¶

Stop the Event Services and MariaDB Importer¶

Go to the Services UI:

Configuration -> Broker Control -> Services
Find and select any running event based service(s).
Click on the "Stop" button.
Find and select the MariaDB Replication Data Importer service.
Click on the "Stop" button.

Modifying the Real-time Events Table¶

Start a command line session to the server with the real-time database.
Login to the database using the Assure1 shortcut:
```
a1dbroot
```
Note

If a "command not found" error is returned, the following can be run to load the Assure1 shortcuts:
```
source $A1BASEDIR/.bashrc
```
Next, run "a1dbroot" again.
Switch to the Event database:
```
use Event
```
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 MariaDB:
```
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 MariaDB 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 MariaDB database, but would need to be repeated for each additional field.

Start the MariaDB Importer¶

Go to the Services UI:

Configuration -> Broker Control -> Services
Find and select the MariaDB Replication Data Importer service.
Click on the "Start" button.

Update Rules Files (Optional)¶

Go to the Rules UI:

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

Start the Event Services¶

Go to the Services UI:

Configuration -> Broker Control -> Services
Find and select any event based service(s) that was previously running.
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.

Go to the Elasticsearch Management UI:

Analytics -> Events -> Management
In the Kibana section, click on the "Index Patterns" link.
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¶

During initial deployment, make sure the application configuration has the LogLevel set to DEBUG. After functionality has been verified, this can be changed 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 stored in the "common" folder of the rules repository. The files are then available to all servers without needing to manually manage individual files:
- Go to the Rules UI:
  
  Configuration -> Rules
- Expand the following folder path: Core Rules (rules) -> Default read-write branch (default) -> collection -> event -> common.
- New/updated files should be here so they are available for all applications.

Steps¶

Updating Custom Fields on Deduplication¶

To get Assure1 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.

The InsertSQLFile (usually called InsertSQLFile.sql) contains a prepared statement that controls which fields are inserted when an event is initially received, or which fields are updated when a duplicate event is received. The generic statement is as follows, but with some modifications to it as shown in the "** **" sections. These should be updated before saving the file.

In the column list, the **NewField1** tag should be entirely replaced with the actual field name, and each additional field should be added to this list.
In the VALUES list, the **?** tag should be replaced with a single question mark ("?"). However, a separate question mark should be added for each additional field that is being added to the list.
In the ON DUPLICATE list, the **NewField1** tag should be replaced with the actual field name. Other MySQL functionality is also possible here.

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

The FieldSetFile (usually called FieldSetFile.sql) contains the information used by the application that determines what fields are used to replace the placeholders (the "?") in the InsertSQLFile prepared statement. Each placeholder must correspond with its respective field. The generic statement is as follows, but the **NewField1** should be entirely replaced with the actual field name, and each additional field should be added to this list. This should be updated before saving the file.
```
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**
```

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:

Go to the Services or Jobs UI:

Configuration -> Broker Control -> Services

Configuration -> Broker Control -> Jobs
Find and select the application that will be using the new configuration files.
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).
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