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)
To enable MySQL collection perform the following steps:
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/
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)
Parameter | Value |
---|---|
Database | Database name |
SERVER | Database server IP |
PORT | Database server listening port |
Driver | Driver path |
In my example:
<?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>
<?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:
< <(opening angle bracket)
> >(closing angle bracket)
& &(ampersand)
" "(double quotation mark)
-->
<HEADER
id1="0001"
id2="0001"
content="<messageid>:<!payload>"/>
<MESSAGE
level="5"
parse="1"
parsedefvalue="1"
tableid="47"
id1="%mysql_audit"
id2="%mysql_audit"
eventcategory=""
content="<sessionid>||<username>||<action>||<event_time>"/>
</DEVICEMESSAGES>
To test MySQL collection:
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)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.