Database export

Introduction

Tranalyzer can report its information in various databases, provided the right plugin is loaded. The supported databases and associated plugins are:

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.9.2):

Dependencies

• ClickHouse:

  See below.

• 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
  macOS	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
  macOS	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
  macOS	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
  macOS	brew install postgresql

• SQLite:

  Ubuntu	sudo apt-get install libsqlite3-dev
  Arch	sudo pacman -S sqlite
  openSUSE	sudo zypper install sqlite3-devel
  Red Hat/Fedora	sudo dnf install sqlite-devel or sudo yum install sqlite-devel
  macOS	brew install sqlite

ClickHouse dependencies

The clickhouseSink plugin depends on the clickhouse-cpp and clickhouse libraries.

There is no package for the clickhouse-cpp library, you can install it from source with the following commands:

git clone https://github.com/clickhouse/clickhouse-cpp

cd clickhouse-cpp

mkdir build .

cd build

cmake ..

make

sudo make install

If /usr/local/lib/ is not in your library path, you can add it with:

echo "/usr/local/lib/" | sudo tee -a /etc/ld.so.conf.d/mylibs.conf

sudo ldconfig

Ubuntu

ClickHouse from the main repository is out of date and might not support all features. To get an up to date version, you can add ClickHouse repository:

sudo apt-get install apt-transport-https ca-certificates dirmngr

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4

echo "deb https://repo.clickhouse.com/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list

sudo apt-get update

sudo apt-get install -y clickhouse-server clickhouse-client

sudo service clickhouse-server start

CentOS or RedHat

sudo yum install yum-utils

sudo rpm --import https://repo.clickhouse.com/CLICKHOUSE-KEY.GPG

sudo yum-config-manager --add-repo https://repo.clickhouse.com/rpm/clickhouse.repo

sudo yum install clickhouse-server clickhouse-client

sudo /etc/init.d/clickhouse-server start

Arch Linux

yay -S clickhouse-server-bin clickhouse-client-bin clickhouse-common-static-bin

sudo systemclt start clickhouse-server

Note that you can use your favorite AUR helper instead of yay.

openSUSE

sudo zypper install clickhouse

macOS

wget https://builds.clickhouse.com/master/macos/clickhouse

chmod a+x ./clickhouse

./clickhouse server

Required plugins

The only required plugin is the one associated to the database:

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 analyze your data!

ClickHouse

To use the clickhouseSink plugin, a ClickHouse server needs to be running. Default settings can be changed in clickhouseSink/src/clickhouseSink.hpp.

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.

To access the output after a tranalyzer run with the clickhouseSink plugin active:

clickhouse-client -d tranalyzer

-- Number of flows
:) SELECT count(*) FROM flow;

-- 10 first srcIP/dstIP pairs
:) SELECT "srcIP", "dstIP" FROM flow LIMIT 10;

-- All flows from 1.2.3.4 to 1.2.3.5
:) SELECT * FROM flow WHERE "srcIP.1" = 4 AND "srcIP.2" = '1.2.3.4' AND
                            "dstIP.1" = 4 AND "dstIP.2" = '1.2.3.5';

MAC addresses are stored in binary, user defined functions help and are supported in newer ClickHouse versions.

:) CREATE FUNCTION
    toMacStr AS (binMac) ->
        arrayStringConcat(
            arrayFlatten(
                extractAllGroups(
                    hex(binMac), '(..)'
                )
            ),
        ':'
        )

:) CREATE FUNCTION
    toMacStrArray AS (binMacArray) ->
        arrayMap(x -> toMacStr(x), binMacArray)

With these functions, MAC addresses can be displayed in human readable form:

:) SELECT
    toMacStrArray(srcMac) AS srcMacStr
FROM
    flow
LIMIT 10

To clean up an existing database:

clickhouse-client

:) DROP DATABASE tranalyzer;

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 behavior 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])
#])

First, run Tranalyzer:

t2 -r file.pcap

Then, connect to the MySQL database:

mysql -u mysql -D tranalyzer

-- Number of flows
mysql> SELECT COUNT(*) FROM flow;

-- 10 first srcIP/dstIP pairs
mysql> SELECT "srcIP", "dstIP" FROM flow LIMIT 10;

-- All flows from 1.2.3.4 to 1.2.3.5
mysql> SELECT * FROM flow WHERE "srcIP" = '1.2.3.4' AND "dstIP" = '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 behavior can be changed by editing the file mongoSink/src/mongoSink.h.

First, run Tranalyzer:

t2 -r file.pcap

Then, connect to the Mongo database:

mongosh tranalyzer

// Number of flows
> db.flow.countDocuments()

// 10 first srcIP/dstIP pairs
> db.flow.find({}, { _id: 0, srcIP: 1, dstIP: 1 }).limit(10)

// All flows from 1.2.3.4 to 1.2.3.5
> db.flow.find({ srcIP: "1.2.3.4", dstIP: "1.2.3.5" })

// Clean up an existing database
> db.flow.drop()

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. This can be achieved with the following commands:

psql postgres

postgres=# CREATE ROLE postgres WITH LOGIN PASSWORD 'postgres';
CREATE ROLE

postgres=# ALTER ROLE postgres CREATEDB;
ALTER ROLE

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 behavior can be changed by editing the file psqlSink/src/psqlSink.h.

First, run Tranalyzer:

t2 -r file.pcap

Then, connect to the PostgreSQL database:

psql -U postgres -d tranalyzer

-- Number of flows
tranalyzer=# SELECT COUNT(*) FROM flow;

-- 10 first srcIP/dstIP pairs
tranalyzer=# SELECT "srcIP", "dstIP" FROM flow LIMIT 10;

-- All flows from 1.2.3.4 to 1.2.3.5
tranalyzer=# SELECT * FROM flow WHERE "srcIP" = '1.2.3.4' AND "dstIP" = '1.2.3.5';

To clean up an existing database:

psql -U postgres

tranalyzer=# DROP DATABASE tranalyzer;

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 t2 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 behavior can be changed by editing the file sqliteSink/src/sqliteSink.h.

First, run Tranalyzer:

t2 -r file.pcap

Then, connect to the SQLite database:

sqlite3 file.db

-- Number of flows
sqlite> SELECT COUNT(*) FROM flow;

-- 10 first srcIP/dstIP pairs
sqlite> SELECT "srcIP", "dstIP" FROM flow LIMIT 10;

-- All flows from 1.2.3.4 to 1.2.3.5
sqlite> SELECT * FROM flow WHERE "srcIP" = '1.2.3.4' AND "dstIP" = '1.2.3.5';