Tutorial: Database Export

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

  • MariaDB:

    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 or sudo yum install MariaDB-devel
    Mac OS X brew install mariadb-connector-c

  • MongoDB:

    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 or sudo yum install mongo-c-driver-devel
    Mac OS X brew install mongo-c-driver

  • MySQL:

    Ubuntu sudo apt-get install libmysqlclient-dev
    Gentoo sudo emerge mysql-connector-c
    Red Hat/Fedora sudo dnf install community-mysql-devel or sudo yum install community-mysql-devel
    Mac OS X brew install mysql-connector-c

  • PostgreSQL:

    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 or sudo yum install libpq-devel
    Mac OS X brew install postgresql

  • SQLite:

    Ubuntu sudo apt-get install libsqlite3-dev
    openSUSE sudo zypper install sqlite3-devel
    Red Hat/Fedora sudo dnf install sqlite-devel or sudo 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