Integrating a MySQL (community) database with NetWitness for Logs

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

 

 
  
 
  
   
  
   mysql_audit
  
  
   
  
   odbc
  
  
   
  
   Mysql Custom Auditing
  
  
   
  
   1.0
  
  
   
  
   Andreas Funk
  
  
   
  
   Mysql SQL for Testing
  
  
 
  
   
  
      
   
    mysql_audit
   
   
  
 
  
   
  
   
   
  
  
 
  
   
  
   
      
   
    
         
    
     
            
     
      mysql_audit
     
     
            
     
      ||
     
     
            
     
      30
     
     
            
     
      
               SELECT ID, Username, Action, TimeOfAction FROM audit WHERE ID > '%TRACKING%' ORDER BY ID ASC
      
            
     
     
            
     
      ID
     
     
            
     
      SELECT MAX(ID) FROM audit
     
     
            
     
      ID
     
     
         
    
    
      
   
   
   
  
  

 

• Next create a parser to match this specification in /etc/netwitness/ng/envision/etc/devices/yourDeviceName. My simple example looks as follows:

 
 

 
  
 
  
        
  
                   xml="1"
   
                checksum=""
   
                revision="0"
   
                enVision=""
   
                device="2.0"/>
   
 
   

   
   

   
   
        
   
                    id1="0001"
    
                id2="0001"
    
                content="<messageid>:<!payload>"/>
    
 
    
        
    
                     level="5"
     
                parse="1"
     
                parsedefvalue="1"
     
                tableid="47"
     
                id1="%mysql_audit"
     
                id2="%mysql_audit"
     
                eventcategory=""
     
                content="<sessionid>||<username>||<action>||<event_time>"/>
     
 
     

    
   
  
 

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