Tutorial: Database Export

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

• MongoDB: mongoSink
• MySQL: mysqlSink
• PostgreSQL: psqlSink
• SQLite: sqlteSink

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 the file README.md or to the documentation 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.1lm/trunk):

Dependencies

• MongoDB:

  • Ubuntu: sudo apt-get install libmongoc-dev
  • Arch: sudo pacman -S mongo-c-driver
  • Mac OS X: brew install mongo-c-driver

• MySQL:

  • Ubuntu: sudo apt-get install libmysqlclient-dev
  • Mac OS X: brew install mysql-connector-c

• PostgreSQL:

  • Ubuntu: sudo apt-get install libpq-dev
  • Arch: sudo pacman -S postgresql-libs
  • Mac OS X: brew install postgresql

• SQLite:

  • Ubuntu: sudo apt-get install libsqlite3-dev
  • Arch: sudo pacman -S sqlite
  • Mac OS X: brew install sqlite

Required plugins

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

• 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 build 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!

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" }).limit(10)        # All flows from 1.2.3.4 to 1.2.3.5

For examples of more complex queries, have a look in scripts/t2fm/mongo/.

MySQL

The mysqlSink plugin requires a 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.

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.

> 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

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 scripts/t2fm/psql/.

SQLite

The sqliteSink plugin creates a database (file) named tranalyzer.db in the /tmp/ folder 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 tranalyzer.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