Tutorial: Database Export
Contents
Tranalyzer can report its information in various databases, provided the right plugin is loaded. The supported databases and associated plugins are:
MariaDB | mysqlSink |
MongoDB | mongoSink |
MySQL | mysqlSink |
PostgreSQL | psqlSink |
SQLite | sqliteSink |
Prerequisites
For this tutorial, it is assumed the user has a basic knowledge of Tranalyzer and that the file t2_aliases
has been sourced in ~/.bashrc
or ~/.bash_aliases
as follows (Refer to How to install Tranalyzer for more details):
# $HOME/.bashrc
if [ -f "$T2HOME/scripts/t2_aliases" ]; then
. "$T2HOME/scripts/t2_aliases" # Note the leading `.'
fi
Make sure to replace $T2HOME
with the actual path, e.g., $HOME/tranalyzer2-0.8.9
):
Dependencies
-
Ubuntu sudo apt-get install libmariadb-dev
Arch sudo pacman -S mariadb-libs
Gentoo sudo emerge mariadb-connector-c
openSUSE sudo zypper install libmariadb-devel
Red Hat/Fedora sudo dnf install MariaDB-devel
orsudo yum install MariaDB-devel
Mac OS X brew install mariadb-connector-c
-
Ubuntu sudo apt-get install libmongoc-dev
Arch sudo pacman -S mongo-c-driver
Gentoo sudo emerge mongo-c-driver
Red Hat/Fedora sudo dnf install mongo-c-driver-devel
orsudo yum install mongo-c-driver-devel
Mac OS X brew install mongo-c-driver
-
Ubuntu sudo apt-get install libmysqlclient-dev
Gentoo sudo emerge mysql-connector-c
Red Hat/Fedora sudo dnf install community-mysql-devel
orsudo yum install community-mysql-devel
Mac OS X brew install mysql-connector-c
-
Ubuntu sudo apt-get install libpq-dev
Arch sudo pacman -S postgresql-libs
Gentoo sudo emerge postgresql
openSUSE sudo zypper install postgresql-devel
Red Hat/Fedora sudo dnf install libpq-devel
orsudo yum install libpq-devel
Mac OS X brew install postgresql
-
Ubuntu sudo apt-get install libsqlite3-dev
openSUSE sudo zypper install sqlite3-devel
Red Hat/Fedora sudo dnf install sqlite-devel
orsudo yum install sqlite-devel
Mac OS X brew install sqlite
Required plugins
The only required plugin is the one associated to the database:
MariaDB | t2build mysqlSink |
MongoDB | t2build mongoSink |
MySQL | t2build mysqlSink |
PostgreSQL | t2build psqlSink |
SQLite | t2build sqlteSink |
For this tutorial, we will also load the basicFlow, basicStats and tcpStates plugins. Although not required, those plugins provide useful information, such as source and destination addresses and ports, protocols and basic statistics about packets and bytes. They can be built by running:
$ t2build basicFlow basicStats tcpStates
General Instructions
- Build the plugins you want, e.g.,
t2build basicFlow basicStats dnsDecode tcpStates
- Build the plugin for the database you want to use, e.g.,
t2build psqlSink
(more details on specific configurations below) - Run Tranalyzer:
t2 -r file.pcap
- Access your database and analyse your data!
MariaDB/MySQL
The mysqlSink plugin requires a MariaDB/MySQL server running on 127.0.0.1
on port 3306
. In addition, a user mysql
with password mysql
MUST exist and have create and write access. This can be achieved as follows:
$ sudo mysql -u root mysql
...
MariaDB [mysql]> create user 'mysql'@'localhost' identified by 'mysql';
MariaDB [mysql]> grant all privileges on *.* to 'mysql'@''localhost' with grant option;
The plugin creates a database named tranalyzer
and a table named flow
. By default, the plugin will reuse the DB if it already exists and append new data to the table if it already exists.
Note that all those values and behaviour can be changed by editing the file mysqlSink/src/mysqlSink.h
.
If both libraries are present, MariaDB will be used. In this case, MySQL can be forced by commenting lines 37 and 39 in mysqlSink/configure.ac as follows:
#PKG_CHECK_MODULES([LIBMYSQL], [libmariadb], [], [
PKG_CHECK_MODULES([LIBMYSQL], [mysqlclient])
#])
$ t2 -r file.pcap # Run Tranalyzer
$ mysql -u mysql -D tranalyzer # Connect to the MySQL database
mysql> select count(*) from flow; # Number of flows
mysql> select "srcIP", "dstIP" from flow limit 10; # 10 first srcIP/dstIP pairs
mysql> select * from flow where "srcIP" = '1.2.3.4' and "dstIP" = '1.2.3.5'; # All flows from 1.2.3.4 to 1.2.3.5
MongoDB
The mongoSink plugin requires a MongoDB server running on 127.0.0.1
on port 27017
.
The plugin creates a database named tranalyzer
and a collection named flow
.
Note that all those values and behaviour can be changed by editing the file mongoSink/src/mongoSink.h
.
$ t2 -r file.pcap # Run Tranalyzer
$ mongo tranalyzer # Connect to the Mongo database
> db.flow.count() # Number of flows
> db.flow.find({}, { _id: 0, srcIP: 1, dstIP: 1 }).limit(10) # 10 first srcIP/dstIP pairs
> db.flow.find({ srcIP: "1.2.3.4", dstIP: "1.2.3.5" }) # All flows from 1.2.3.4 to 1.2.3.5
For examples of more complex queries, have a look in $T2HOME/scripts/t2fm/mongo/
.
PostgreSQL
The psqlSink plugin requires a PostgreSQL server running on 127.0.0.1
on port 5432
. In addition, a user postgres
with password postgres
MUST exist and have create and write access.
The plugin creates a database named tranalyzer
and a table named flow
. By default, the plugin will reuse the DB if it already exists and append new data to the table if it already exists.
Note that all those values and behaviour can be changed by editing the file psqlSink/src/psqlSink.h
.
$ t2 -r file.pcap # Run Tranalyzer
$ psql -U postgres -d tranalyzer # Connect to the PostgreSQL database
tranalyzer=# select count(*) from flow; # Number of flows
tranalyzer=# select "srcIP", "dstIP" from flow limit 10; # 10 first srcIP/dstIP pairs
tranalyzer=# select * from flow where "srcIP" = '1.2.3.4' and "dstIP" = '1.2.3.5'; # All flows from 1.2.3.4 to 1.2.3.5
For examples of more complex queries, have a look in $T2HOME/scripts/t2fm/psql/
.
SQLite
The sqliteSink plugin creates a database (file) whose name and location depend on tranalyzer
input and/or -w/-W
options and a table named flow
. By default, the plugin will append new data to the table if it already exists.
Note that all those values and behaviour can be changed by editing the file sqliteSink/src/sqliteSink.h
.
$ t2 -r file.pcap # Run Tranalyzer
$ sqlite3 file.db # Connect to the SQLite database
sqlite> select count(*) from flow; # Number of flows
sqlite> select "srcIP", "dstIP" from flow limit 10; # 10 first srcIP/dstIP pairs
sqlite> select * from flow where "srcIP" = '1.2.3.4' and "dstIP" = '1.2.3.5'; # All flows from 1.2.3.4 to 1.2.3.5