• Preface
• Enabling MySQL collection
• Testing MySQL collection

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:

   (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.

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

       In my example:

• 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:

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: