Creating an OSSIM plugin to collect logs from the database

1. What is it for?

Suppose you have an application that writes event logs to a database (for example, Kaspersky Security Center, Symantec Endpoint Security Server, and many others) and you want to collect these logs using Alienvault OSSIM (USM) using SIEM, but Out of the box, OSSIM cannot build and parse the logs of your application.

2. Baseline

Alienvault OSSIM software installed on the server (in the example version 5.3 was used).
The database server in which the application, whose event logs are to be collected in OSSIM, records events (for this article, the created test database is used and recorded manually).
OSSIM server IP: 192.168.0.111
Server IP with database: 192.168.0.113
In this example, the MySQL database (hereinafter referred to as the DB) is used with the name “event_data”, in which the data_table table is created to store the event logs.
To access the database, an account has been created (hereinafter - UZ) for the user with the name “mysql_user” and the password “SQL_con_1”. KS user is entitled to connect and read information from the database over the network. All data is stored in one table - “data_table”.
Table layout:

+------------+--------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+-------------------+-------+ | date | timestamp | NO | | CURRENT_TIMESTAMP | | | event_id | int(11) | NO | PRI | 0 | | | event_type | varchar(40) | YES | | NULL | | | username | varchar(30) | YES | | NULL | | | message | varchar(100) | YES | | NULL | | +------------+--------------+------+-----+-------------------+-------+ 

Example of filling the table:

 +---------------------+----------+------------+----------+----------------+ | date | event_id | event_type | username | message | +---------------------+----------+------------+----------+----------------+ | 2016-06-26 22:22:09 | 159 | type1 | unknown | Message text | | 2016-06-26 22:22:10 | 160 | type1 | valeriy | Message text | | 2016-06-26 22:22:11 | 161 | type1 | alex | Message text | | 2016-06-26 22:22:12 | 162 | type1 | cisco | Message text | | 2016-06-26 22:22:14 | 163 | type1 | net_adm | Message text | +---------------------+----------+------------+----------+----------------+ 

3. Decision

Before you begin to solve this problem, we will determine the terminology used.
The source (event log) (IB) is software (in this case, a database) or a hardware system whose event logs are transmitted to OSSIM for processing and storage.
Type of source - the whole set of sources can be divided into groups - types of sources (for example, a network of 5 identical routers represents 5 sources, but all of them are of the same type).
Plugin is a mechanism configured in OSSIM for receiving and processing event logs from sources. For each type of source you need your own plugin with a unique number (plugin_id).
Plug-in type is one of 4 types available in OSSIM — database, log, sdee, wmi.
Event class - the entire set of events generated by the source can be divided into different classes. The allocation of classes is made by expert. For example, the source in this example has several event classes - “type 1”, “connect success”, “suspicious activity”. The event class ID is “plugin_sid”.
For clarity, the table below.

Table 1 - Sources and Classes of Events

A source	Source type	Plugin	Plugin type	Plugin_id	Event class	Plugin_sid
Database server 192.168.0.113	DB mysql applications "test"	Mysql_logs	database	9020	type 1	one
					connect success	2
					suspicious activity	3

To solve the problem, you must perform the following steps:

1. Create the OSSIM plug-in .cfg configuration file in the / etc / ossim / agent / plugins / folder (this is the main configuration file containing settings for connecting to the database and settings for parsing received events);
2. Create the OSSIM plug-in .sql configuration file in the / usr / share / doc / ossim-mysql / contrib / plugins / folder (this file describes the new plug-in types — its name, event class names, their prioritization, etc.) and download information about the new plugin in OSSIM plugin database;
3. Include the created plugin.

3.1. Configuration file .cfg

This configuration file is required to configure the main parameters of the plugin - how to get event logs, credentials to connect to the event source, the parser (if it exists), the normalization scheme of data obtained from the event.
You can select an arbitrary name for this file, the main thing is that the file extension has a .cfg, for example, db_logs.cfg, cisco-router.cfg, etc. In this example, the file is called db_logs.cfg.
The configuration file contains a number of sections, each of which is designated: [<section name>].
')
Section "DEFAULT"
In this section, there is only one parameter - the unique identifier of the plugin “plugin_id”. Each plug-in has its own and must be unique within the framework of one OSSIM installation (not to be confused with the class identifier of the plugin_sid event, this identifier is unique within the same source). Numbers 9000 to 10000 are reserved for the created plug-ins, however, to make sure if the number you chose is free, you can connect to the OSSIM server via ssh, select the “Jailbreak” menu and, once in the console, execute:
grep "plugin_id =" /etc/ossim/agent/plugins/*.cfg | grep <required number>
A line appears in the output with the name of the plug-in in which the selected ID number is used. If there is no output, then the number is free and can be used.

Section "config"
In this section, there are many parameters that are responsible for connecting to the source, the type of plug-in, etc.
The "type" parameter tells what type of system this plugin is. There are two of them - "detector" and "monitor". “Detection” plugins are designed to get event logs from sources and extract useful information (parsing) from them. “Monitor” plugins receive information from sources and check the status of monitored parameters at the time of the request. Those. Monitor plug-ins monitor various source indicators. To perform our task, it is necessary to choose the type of the “detector” plugin.
The parameter “enable” is responsible for the status of the plugin (on / off). For the plugin to work it must be set to “yes” (Thank you, cap!).
The “source” parameter sets the source type and tells the plugin which of the mechanisms available in OSSIM should be used to receive event logs, it can be “database” (for mssql and mysql databases), “log” (for files), “sdee” ( logs of cisco devices), “wmi” (Windows event logs received via the WMI protocol). In our case, the value of this parameter should be set to "database".
The parameters “source_type”, “source_ip”, “source_port”, “user”, “password”, “db”, “sleep” set the parameters for connecting OSSIM to the source to receive event logs. The “source_type” parameter can take two values ​​- “mysql” and “mssql” - depending on the source database. The remaining parameters specify the settings for connecting to the database:

• “Source_ip” is the IP address of the database server;
• "Source_port" - the port on which the database on the server responds;
• “User” - the user (do not forget to give him permission to connect to the database via the network and also configure the firewall, if any);
• “Password” - user password;
• "Db" - the name of the database;
• “Sleep” is the interval of OSSIM access to the database in seconds.
• The parameters “process”, “start”, “stop” refer to the settings of processes that generate event logs and are locally, i.e. on the OSSIM server. For this configuration, these fields must be left blank.

Section "translate"
This section is intended to assign classes to source events. Classes are assigned depending on the value of a specific field extracted by parsing from a “raw” event. Each line of this section has the form: <word or phrase> = <code>. For the example in question, events having a selected field (how the field is selected, see below in the section “query”) the value “type 1” receive the code “1”, “connect success” - 2, etc.

Section "start_query"
The section describes the query (the “query” parameter), with which the identifier is obtained, in relation to which the events will be treated as new (not processed by OSSIM). For example, in the database used by me there is a field “event_id” - an event number increasing by 1 for each new event. This request receives the number (event_id) of the last event in the database at the time of the request and saves it. Events with the value of this “marker” greater than the value saved the previous time will be considered new arrivals and will be processed by OSSIM.
Please note that for the database plugin, the regexp parameter must be left blank.

Section "query"
The section describes the acquisition and parsing (parsing) of data from the database.
In the first parameter, “query”, there is a query to the database, with the help of which information should be obtained, and later placed in the fields of the OSSIM scheme. The scheme is a number of fixed fields that describe each event (for example, the source and destination IP addresses, user name, ports, additional service information, etc.) falling into OSSIM. More information about the scheme can be found in the official documentation for OSSIM.
In this example, the query text “select event_id, date, event_type, username, message from data_table” returns all the values ​​of the columns “event_id”, “date”, “event_type”, “username”, “message” from the table “data_table”. The last part of the request, namely “... where event_id> $ 1;” just refers us to the “start_query” section and informs OSSIM that it is not necessary to collect ALL events from the database, but only those that have an event mark “event_id” more than its last saved value (which was saved by the previous query).
The parameter "regexp" must be left empty, and "ref" is equal to "0".
All the parameters that follow describe the normalization of information (i.e., its distribution over the fields of the OSSIM scheme) obtained by the query specified in the “query” parameter. They are:
<field name in the OSSIM scheme> = <function - optional parameter> <$ request field number>.
It should be noted here that the counting of the request fields starts with "0" (Query text: select event_id, date, event_type, username, message from data_table. Field numbers: 0 - "event_id", 1 - "date", 2 - "event_type" , 3 - “username”, 4 - “message”). Therefore, in this example, the information of the field following the fourth in the request, i.e. "Username", but the call to it is made for $ 3. Therefore, it is advisable to include the very unique value first in the query “query” - the event identifier (as done in the example - “event_id”).
The functions used in these parameters are needed for:

1. Transmitting the event timestamp in the correct format to OSSIM;
2. Event class definitions

OSSIM can recognize the time stamp in various formats, more on this in the “Alienvault Building Connector Plugins” document. This configuration string:

 date={normalize_date($1)} 

informs OSSIM that in the parameter “1” the timestamp is transmitted, which needs to be converted to the format used by OSSIM. If the format of the transmitted timestamp does not match the supported OSSIM, the current date of the OSSIM server will be placed in the "date" field.

Highlight class event. Above, we have already said that the events of this type of source have 3 classes: “type 1”, “connect success”, “suspicious activity”. In this example, their definition is associated with the value of the "event_type" field. This is indicated by the line:

 plugin_sid={translate($2)} 

“Plugin_sid” is an event class identifier (must be unique within the same source type). The above configuration string informs OSSIM that the plugin_sid for each event will be calculated by comparing the value in the “2” field with the data given in the “translate” section of this configuration file. Those. events with a “type 1” value in the “event_type” field will be assigned an identifier “plugin_sid” with a value of “1”, events for which “event_type” = “suspicious activity” is plugin_sid = 2, etc. Further, this identifier will be used in the .sql configuration file (see below). If there is only one event class, then you can not use the “translate” function, but simply specify plugin_sid = 1.
Configuration example .cfg:

 [DEFAULT] plugin_id=9020 [config] type=detector enable=yes source=database source_type=mysql source_ip=192.168.0.113 source_port=3306 user=mysql_user password=SQL_con_1 db=event_data sleep=2 process= start=no stop=no [translation] type 1=1 connect success=2 suspicious activity=3 [start_query] query="select event_id from data_table order by event_id desc limit 1;" regexp= [query] query="select event_id, date, event_type, username, message from data_table where event_id > $1;" #order by event_id desc limit 1 regexp= ref=0 date={normalize_date($1)} plugin_sid={translate($2)} username={$3} userdata1={$4} userdata2={$2} 

3.2. Configuration file .sql

The configuration file .sql, located in the / usr / share / doc / ossim-mysql / contrib / plugins folder, is required to configure the display of events from the source in the OSSIM interface, assign Reliability and Priority values ​​to different classes of source events (based on which ).
In fact, this file is a guide to creating the necessary OSSIM database records to describe the source.
An example of the .sql file created for this example is shown below.
The first two terms erase all data from the database describing the source with the specified plugin ID, in the example, 9020 (this number should correspond to the “plugin_id” from the “Default” section of the .cfg configuration file).

 DELETE FROM plugin WHERE id="9020"; DELETE FROM plugin_sid WHERE plugin_id="9020"; 

The following string describes the new plugin - sets it to “id” (corresponds to “plugin_id” from the .cfg file), “type” (should be set to “1”), the name (which will be displayed in the OSSIM interface), and an arbitrary description in description field:

 INSERT IGNORE INTO plugin (id, type, name, description) VALUES (9020, 1, "Mysql_logs", "Logs from mysql database"); 

Next come the lines describing the various classes of events. For each class - a separate line. The line specifies the values ​​of the parameters “plugin_id”, “sid” (should correspond to the number assigned to the class in the “translate” section of the .cfg file), category_id, subcategory_id (the category and subcategory number. The list of categories and subcategories can be viewed in the files: / usr / share / ossim-taxonomy / alienvault-subcategory.sql and alienvault-categories.sql). The "class_id" parameter must be set to "null". The “name” parameter indicates the name of the event class that will be displayed in the OSSIM interface, the “reliability” and “priority” parameters are integer values ​​from 1 to 5.

 INSERT IGNORE INTO plugin_sid(plugin_id, sid, category_id, subcategory_id, class_id, name, priority, reliability) VALUES (9020, 1, 11, 139, null, "Mysql logs: Type 1", 1, 1); 

The full text of the .sql file (in the example, it is named db_logs.sql):

 DELETE FROM plugin WHERE id="9020"; DELETE FROM plugin_sid WHERE plugin_id="9020"; INSERT IGNORE INTO plugin (id, type, name, description) VALUES (9020, 1, "Mysql_logs", "Logs from mysql database"); INSERT IGNORE INTO plugin_sid(plugin_id, sid, category_id, subcategory_id, class_id, name, priority, reliability) VALUES (9020, 1, 11, 139, null, "Mysql logs: Type 1", 1, 1); INSERT IGNORE INTO plugin_sid(plugin_id, sid, category_id, subcategory_id, class_id, name, priority, reliability) VALUES (9020, 2, 11, 139, null, "Mysql logs: Connection successfull", 1, 1); INSERT IGNORE INTO plugin_sid(plugin_id, sid, category_id, subcategory_id, class_id, name, priority, reliability) VALUES (9020, 3, 11, 139, null, "Mysql logs: Suspicious activity", 1, 1); 

3.3. Enable plugin

To enable the plug-in, you need to add information about the created plug-in to the OSSIM database by executing the script file .sql created at the previous stage, and then activate the plug-in.
To add information about the plugin to the database, run the following command:

 # ossim-db < /usr/share/doc/ossim-mysql/contrib/plugins/< .sql> 

In this example:

 # ossim-db < /usr/share/doc/ossim-mysql/contrib/plugins/db_logs.sql 

To activate the plug-in, you need to connect to the OSSIM server via ssh, go to the “Configure sensor” - “Configure Data Source Plugins” menu, and in the list that appears, find the plug-in name (it corresponds to the “name” field in the .sql configuration file), set next to Him "X" by pressing the spacebar. Return to the main menu by pressing “OK” several times. In the main menu, select the item “Apply” and “OK” and wait for the restart of the OSSIM services.
The plugin is ready to go.

Figure 1 - OSSIM Main Menu


Figure 2 - Menu "Configure sensor"


Figure 3 - Menu "Configure Data Source Plugins"

The figure below shows an example of the displayed plug-in events in the OSSIM GUI.

Figure 4 - Event mapping in the OSSIM interface

4. Diagnostics and troubleshooting

To diagnose faults, you can do the following:
1) Check the connection from the OSSIM server to the database server where the event logs are taken from:

 #mysql –u <username> -p –h <  > 

After entering the password, select the database and execute a query to it, specified in the “query” field:

 mysql> use event_data; mysql> select event_id, date, event_type, username, message from data_table; 

2) Check that the new plugin records are present in the OSSIM database with a query:

 # ossim-db mysql> use alienvault; mysql> select id,name from plugin where id='<   plugin_id>'; 

3) Check the agent log file for errors and alarms:

 # grep –E “Discarded|ERR|WARN” /var/log/alienvault/agent/agent.log 

5. Additional materials

Alienvault official documents for setting up plugins:
Alienvault Building Collector Plugins
Creating new plugin