Connect to ClickHouse

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

clickhouse;<host[@<tcp-port>,<mysql-port>]|unix-socket>;<dbname>;<user>;<pw>

or

clickhouse-cluster;<host[@<tcp-port>,<mysql-port>]|socket>;<dbname>;<user>;<pw>;<cluster name>

Where

  • <host[@mysqlport]|socket> Specifies the database host or a socket file. By default, port 9000 is used for the connection via native API and 9004 for connection over MySQL API. To use a different port, specify it with @TCP_PORT,MYSQL_PORT. The host can be a symbolic name or an IP address. By default ntopng connects in clear text, this unless you want to do it over TLS and in this case you need to append a ‘s’ after the port. Example 192.168.2.1@9000,9004s. Please see later in this pare more information about TLS connections.

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

  • <cluster name> Specifies the name of the ClickHouse cluster <user>

If you use a stand-alone ClickHouse database you need to use -F clickhouse;.... whereas with a cluster you need to use -F clickhouse-cluster;....

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"

The above example with a ClickHouse cluster would be:

./ntopng -F "clickhouse-cluster;127.0.0.1;ntopng;default;default;ntop_cluster"

What’s Stored in ClickHouse

ntopng stores both 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.

For more information, check the ntopng database schema.

TLS Connection

In order to connect ntopng with ClickHouse using a secure TCP connection, first, create the server certificate with the following command:

openssl req -subj "/CN=localhost" -new -newkey rsa:2048 -days 365 -nodes -x509 -keyout /etc/clickhouse-server/server.key -out /etc/clickhouse-server/server.crt

This command generates a server certificate for secure communication between ntopng and ClickHouse, establishing a secure TCP connection.

Note

To enable the ClickHouse user to use the server.crt and server.key files, it is necessary to change their owner. Run the following command as a superuser to grant the required permissions:

chown clickhouse:clickhouse /etc/clickhouse-server/server.key /etc/clickhouse-server/server.crt

Open the ClickHouse config.xml file and uncomment the following lines:

  • <!--<tcp_port_secure>9440</tcp_port_secure>-->

  • <!--<certificateFile>/etc/clickhouse-server/server.crt</certificateFile>-->

  • <!--<privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>-->

Restart ClickHouse.

Start ntopng using the -F option, but in this case, it is mandatory to indicate the database port with an s at the end of it.

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

For example:

./ntopng -F "clickhouse;127.0.0.1@9000,9440s;ntopng;default;default`

Securing the Connection in ClickHouse Cloud

To secure the connection in ClickHouse Cloud, instead, the only thing to configure is adding the s character in the -F option after the ports list, when starting ntopng (or in the configuration file), without needing to configure anything else. For example:

./ntopng -F "clickhouse-cloud;127.0.0.1@9440,3306s;ntopng;default,default;default`

Note

Securing the connection when using ClickHouse Cloud is highly recommended, moreover ClickHouse Cloud by default only accepts secured connections