ClickHouse

ntopng integrates with ClickHouse to store historical flows and alerts. ClickHouse is an high-performance SQL database. To install ClickHouse refer to the official guide.

Note

The ClickHouse database can be executed anywhere, both locally on the machine running ntopng or on a remote machine. However, clickhouse-client must always be installed locally as it is used by ntopng to connect to ClickHouse. This installation guide explains how to install it.

To connect ntopng to ClickHouse use option -F. The format of this option is the following

clickhouse;<host[@port]|socket>;<dbname>;<user>;<pw>

Where

  • <host[@port]|socket> Specifies the database host or a socket file. By default, port 9000 is used for the connection. To use a different port, specify it with @port. The host can be a symbolic name or an IP address.
  • <dbname> Specifies the name of the database to be used and defaults to ntopng
  • <user> Specifies an user with read and write permissions on <dbname>
  • <pw> Specifies the password that authenticates <user>

Example

To connect ntopng and ClickHouse, both running on the same machine, the following line can be used

./ntopng -F="clickhouse;127.0.0.1;ntopng;default;default"

In the example above, 127.0.0.1 is used to connect using IPv4 (using the symbolic string localhost could resolve to an IPv6 address). A user default, identified with password default, with read and write permissions on database ntopng is indicated as well. As shortcut you can use -F clickhouse for F="clickhouse;127.0.0.1;ntopng;default;default"

Stored Information

ntopng stores historical flows and alerts in ClickHouse.

IPv4 and IPv6 flows are stored in table flows. A column INTERFACE_ID is used to identify the interface on which the flow was seen, this is useful ntopng is monitoring multiple interfaces (see -i).

Alerts are stored in several tables, all ending with suffix _alerts. The table prefix indicates the alert family, e.g. host_alerts table contains alerts for hosts, flow_alerts table contains alerts for flows, and so on.

Historical Flows Explorer

When ClickHouse is enabled, an historical flows explorer becomes available in the ntopng web UI.

Note

ClickHouse support is available on all ntopng versions, however, the historical flows explorer is only available in version Enterprise M or above.

The explorer is available from the main menu, under the dashboard section.

Historical Flows Explorer

Historical Flows Explorer

Health

The health of the currently used ClickHouse database can be checked under the System Interface, “Health” page. The status of the database is shown, along with the space used by flow and alert tables.

ClickHouse Health

ClickHouse Health

Similarly, the number of exported and dropped flows can be checked, on a per-interface basis, under the interface stats page. Flow export drops should normally stay at zero or be a tiny fraction of the total amount of exported flows. Drops can occur due to the database being temporarily unreachable or not able to received additional data.

ClickHouse Exported Flows

ClickHouse Exported Flows

Migration from nIndex

To migrate data from nIndex to clickhouse, we have provided the following script https://github.com/ntop/ntopng/blob/dev/tools/nindex_export_to_ch.sh.

The migration script requires as prerequisites executable clickhouse-client and nindex. Both executables must be installed on the machine used for the migration, i.e., the machine containing nIndex files locally.

Here are the steps to perform the migration.

  • Install ClickHouse as described in the guide. This will automatically pull in clickhouse-client.
  • Update the installed ntopng with the most recent development package (version 5.1.211119 or above). This will automatically pull in nindex. For packages already installed it suffices to run apt-get update && apt-get install ntopng (on Debian/Ubuntu) or yum install ntopng (on Centos).
  • Open a shell and download the script
$ curl https://raw.githubusercontent.com/ntop/ntopng/dev/tools/nindex_export_to_ch.sh
  • Edit the downloaded script and set the proper connection parameters. In particular, change HOST, DB_NAME, USER and PWD with a valid ClickHouse host and a user/password pair with read-write privileges on DB_NAME.
  • Execute the script as root and specify the datadir of ntopng using option -d
$ sudo ./nindex_export_to_ch.sh -d /var/lib/ntopng

This will execute the script and perform the necessary migration.

Note

Before executing the migration, make sure to have run ntopng at least one time with ClickHouse. This is necessary to prepare the ClickHouse database structure necessary for the migration.

Example

An ntopng instance was running with nIndex enabled for interface eno1.

$ sudo /usr/bin/ntopng -F "nindex" -i eno1

To replace nIndex with ClickHouse, the following steps are performed.

  • ClickHouse is installed locally following this guide. clickhouse-client can be used to verify connections can be successfully established to the local database
$ clickhouse-client -h 127.0.0.1
ClickHouse client version 21.11.4.14 (official build).
Connecting to 127.0.0.1:9000 as user default.
Connected to ClickHouse server version 21.11.4 revision 54450.

develv5 :)
  • ntopng is updated to the latest development version
$ ntopng --version
Version: 5.1.211119 [Enterprise/Professional build]
  • The configuration of ntopng is changed from nIndex to ClickHouse to make sure it can connect to the ClicHouse server running on the local machine and create the necessary database structure.
sudo /usr/bin/ntopng -F "clickhouse;127.0.0.1;ntopng;flows;default;"
  • Now that the database structure is ready, the migration script is downloaded
$ curl https://raw.githubusercontent.com/ntop/ntopng/dev/tools/nindex_export_to_ch.sh
  • At the beginning of the downloaded script are the ClickHouse database connection parameters. Parameters are adjusted to connect to the ClickHouse server running locally as follows
HOST="127.0.0.1"
USER="default"
PWD=""
DB_NAME="ntopng"
DB_FLOWS="flows"
  • The migration script is executed providing it with the ntopng data directory that contains the nIndex database files
$ sudo ./nindex_export_to_ch.sh -d /var/lib/ntopng/
[...]
Exporting directory: /var/lib/ntopng//plugins0/flows
Done exporting directory: /var/lib/ntopng//plugins0/flows
Job accomplished, all flows have been exported

The migration is now complete and all /flows directories in the ntopng data directory can safely be deleted.