Andreas Funk

Integrating a MySQL (community) database with NetWitness for Logs

Blog Post created by Andreas Funk Employee on Oct 18, 2016

Preface

 

This blog post should help everybody who wants to integrate the free (community) version of the MySQL database with NetWitness for Logs. This blog does NOT describe the MySQL database auditing. Instead the procedure can be used for applications that store their events in the MySQL database.

 

As we do not provide the drivers for that version, it has to be downloaded from http://dev.mysql.com/downloads/connector/odbc/

 

Make sure to get the tar.gz version for EL6. The version downloaded at that the time of writing was mysql-connector-odbc-5.3.4-linux-el6-x86-64bit.tar.gz:

 

MySQL download

   (click the image to enlarge)

 

Enabling MySQL collection

 

To enable MySQL collection perform the following steps:

  • Untar the file obtained from the MySQL website and copy the ODBC driver to the SA ODBC drivers folder:
    tar -xvzf mysql-connector-odbc-5.3.4-linux-el6-x86-64bit.tar.gz
    cp mysql-connector-odbc-5.3.4-linux-el6-x86-64bit/lib/libmyodbc5a.so /opt/netwitness/odbc/lib/

 

  • This is the structure of my example database on 192.168.2.200 Port 3306:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.01 sec)

 

mysql> use test;
    Database changed

    mysql> show tables;
    +----------------+
    
| Tables_in_test |
    +----------------+
    | audit          |
    +----------------+
    1 row in set (0.00 sec)

     mysql> desc audit;
    +--------------+--------------+------+-----+-------------------+-----------------------------+
    | Field        | Type         | Null | Key | Default           | Extra                       |
    +--------------+--------------+------+-----+-------------------+-----------------------------+
    | ID           | int(11)      | YES  |     | NULL              |                             |
    | Username     | varchar(255) | YES  |     | NULL              |                             |
    | Action       | varchar(255) | YES  |     | NULL              |                             |
    | TimeOfAction | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    +--------------+--------------+------+-----+-------------------+-----------------------------+
    4 rows in set (0.00 sec)

 

  • Create the database DSN in Administration > Services > LogCollector > View > Config > Event Sources.

        DSN

 

  • The names for the parameters are different from the names of our default drivers. The following values have to be set:

   

ParameterValue
DatabaseDatabase name
SERVERDatabase server IP
PORTDatabase server listening port
DriverDriver path

 

       In my example:

 

       DSN Values

 

  • Now create the type specification (mine is named mysql_audit.xml) for your database in /etc/netwitness/ng/logcollection/content/collection/odbc/. My example would require the following specification:

 

<?xml version="1.0" encoding="UTF-8"?>
<typespec>
 
   <name>mysql_audit</name>
   <type>odbc</type>
   <prettyName>Mysql Custom Auditing</prettyName>
   <version>1.0</version>
   <author>Andreas Funk</author>
   <description>Mysql SQL for Testing</description>
 
   <device>
      <name>mysql_audit</name>
   </device>
 
   <configuration>
   </configuration>
 
   <collection>
      <odbc>
         <query>
            <tag>mysql_audit</tag>
            <outputDelimiter>||</outputDelimiter>
            <interval>30</interval>
            <dataQuery>
               SELECT ID, Username, Action, TimeOfAction FROM audit WHERE ID > '%TRACKING%' ORDER BY ID ASC
            </dataQuery>
            <trackingColumn>ID</trackingColumn>
            <maxTrackingQuery>SELECT MAX(ID) FROM audit</maxTrackingQuery>
            <trackingColumn>ID</trackingColumn>
         </query>
      </odbc>
   </collection>
</typespec>
  • Next create a parser to match this specification in /etc/netwitness/ng/envision/etc/devices/yourDeviceName. My simple example looks as follows:

 

<?xml version="1.0" encoding="ISO-8859-1" ?>
 
<DEVICEMESSAGES>
 
        <VERSION
                xml="1"
                checksum=""
                revision="0"
                enVision=""
                device="2.0"/>
 
<!--ESI.DeviceClass = Database-->
<!--
If the message tag does not contain a definition of a property,
the default value will be used.
The default values are:
                category="0"
                level="1"
                parse="0
                parsedefvalue="0"
                tableid="1"
                id1=""
                id2=""
                content=""
                reportcategory="0"
                sitetrack="0"
 
The following are the entity reference for all the predefined entities:
&lt;           <(opening angle bracket)
&gt;           >(closing angle bracket)
&amp;          &(ampersand)
&quot;         "(double quotation mark)
 
-->
        <HEADER
                id1="0001"
                id2="0001"
                content="&lt;messageid&gt;:&lt;!payload&gt;"/>
 
        <MESSAGE
                level="5"
                parse="1"
                parsedefvalue="1"
                tableid="47"
                id1="%mysql_audit"
                id2="%mysql_audit"
                eventcategory=""
                content="&lt;sessionid&gt;||&lt;username&gt;||&lt;action&gt;||&lt;event_time&gt;"/>
 
</DEVICEMESSAGES>
  • Finally add the category (name as chosen in your typespec file) and database to the Event Sources and start the ODBC collection:

       Add event source

       Start ODBC collection

 

Testing MySQL collection

To test MySQL collection:

  • Wait for new events to arrive in the database. In my test database I created two events manually:
    mysql> INSERT INTO audit VALUES (7, 'Andreas', 'Login', NOW());
    Query OK, 1 row affected (0.01 sec)
     
    mysql> INSERT INTO audit VALUES (8, 'Andreas', 'Logout', NOW());
    Query OK, 1 row affected (0.00 sec)
     
    mysql> SELECT * FROM audit WHERE ID > 6;
    +------+----------+--------+---------------------+
    | ID   | Username | Action | TimeOfAction        |
    +------+----------+--------+---------------------+
    |    7 | Andreas  | Login  | 2015-08-07 17:27:44 |
    |    8 | Andreas  | Logout | 2015-08-07 17:27:55 |
    +------+----------+--------+---------------------+
    2 rows in set (0.00 sec)
  • Wait for the ODBC collection to get those events. You can verify collection in /var/log/messages:

Aug  7 15:30:22 ld nw[1420]: [OdbcCollection] [info] [mysql_audit.SQL_Audit] [processing] [SQL_Audit] [processing] Published 2 ODBC events: last tracking id: 8

  • The events can now be found in the Investigator with the defined meta generated:

    Navigate view

 

    Events view

Outcomes