Traffic Metrics in ClickHouse

As an alternative to InfluxDB you could store traffic metrics in ClickHouse. This capability is available only for FastNetMon Advanced.

In addition to our legacy / deprecated traffic metrics storage InfluxDB we offer complete support for Clickhouse.

It has many advantages over InfluxDB:

  • Unlimited scalability
  • Predictable memory usage
  • Clustering / redundancy support
  • Ability to store not only metrics but traffic too.

We recommend using Clickhouse for metrics for any network which exceeds 50.000 active hosts.

To enable this capability you will need to install visual graphic stack. It will install and configure all daemons for you.

To enable metrics export to Clickhouse you need to run this command:

sudo fcli set main clickhouse_metrics true
sudo fcli commit

Then you need to set database host:

sudo fcli set main clickhouse_metrics_host 127.0.0.1
sudo fcli commit

By default, we use Clickhouse on same machine but for large installations you may consider running it on another machine and in that case you will need to set external IP address. This field supports hostnames starting from 2.0.348. You can use both IPv4 and IPv6 addresses for clickhouse_metrics_host.

And then set port, we use native TCP/IP port for sending data to Clickhouse: 

sudo fcli set main clickhouse_metrics_port 9000
sudo fcli commit

Set database name:

sudo fcli set main clickhouse_metrics_database fastnetmon
sudo fcli commit

Optionally, you may set username and password:

sudo fcli set main clickhouse_metrics_username default
sudo fcli set main clickhouse_metrics_password ""
sudo fcli commit

You may even configure how often FastNetMon export traffic to Clickhouse, by default it does it every single second to provide real time graphs: 

sudo fcli set main clickhouse_metrics_push_period 1
sudo fcli commit

FastNetMon does not rely on pre-created tables and it can create all required tables in Clickhouse automatically for you.

Clickhouse will create following tables for metrics export:

  • total_metrics, total_metrics_ipv4, total_metrics_ipv6
  • network_metrics, network_24_metrics_ipv4, network_metrics_ipv6
  • host_metrics, host_metrics_ipv6
  • asn_metrics_ipv4, asn_metrics_ipv6
  • system_metrics
  • total_hostgroup_metrics
  • interface_metrics
  • flexible_host_metrics_ipv4, flexible_host_metrics_ipv6
  • flexible_total_hostgroup_metrics

Total traffic counters schema

Tables total_metrics (IPv4+IPv6), total_metrics_ipv4, total_metrics_ipv6 share the same schema and you can find it below:

CREATE TABLE fastnetmon.total_metrics
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `direction` String,
    `flows` UInt64,
    `packets` UInt64,
    `bits` UInt64,
    `tcp_packets` UInt64,
    `udp_packets` UInt64,
    `icmp_packets` UInt64,
    `fragmented_packets` UInt64,
    `tcp_syn_packets` UInt64,
    `dropped_packets` UInt64,
    `tcp_bits` UInt64,
    `udp_bits` UInt64,
    `icmp_bits` UInt64,
    `fragmented_bits` UInt64,
    `tcp_syn_bits` UInt64,
    `dropped_bits` UInt64,
    `schema_version` UInt8 DEFAULT 0 COMMENT '1'
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (direction, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

Network counters schema

All three tables network_metrics (IPv4), network_metrics_ipv6 and network_24_metrics_ipv4 (has information only on /24 basis for IPv4 networks) share the same schema:

CREATE TABLE fastnetmon.network_metrics
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `network` String,
    `packets_incoming` UInt64,
    `packets_outgoing` UInt64,
    `bits_incoming` UInt64,
    `bits_outgoing` UInt64,
    `tcp_packets_incoming` UInt64,
    `tcp_packets_outgoing` UInt64,
    `udp_packets_incoming` UInt64,
    `udp_packets_outgoing` UInt64,
    `icmp_packets_incoming` UInt64,
    `icmp_packets_outgoing` UInt64,
    `fragmented_packets_incoming` UInt64,
    `fragmented_packets_outgoing` UInt64,
    `tcp_syn_packets_incoming` UInt64,
    `tcp_syn_packets_outgoing` UInt64,
    `tcp_bits_incoming` UInt64,
    `tcp_bits_outgoing` UInt64,
    `udp_bits_incoming` UInt64,
    `udp_bits_outgoing` UInt64,
    `icmp_bits_incoming` UInt64,
    `icmp_bits_outgoing` UInt64,
    `fragmented_bits_incoming` UInt64,
    `fragmented_bits_outgoing` UInt64,
    `tcp_syn_bits_incoming` UInt64,
    `tcp_syn_bits_outgoing` UInt64,
    `schema_version` UInt8 DEFAULT 0 COMMENT '1'
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (network, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

Per host counters schema

Both tables host_metrics (IPv4) and host_metrics_ipv6 use following schema:

CREATE TABLE fastnetmon.host_metrics_ipv6
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `host` String,
    `packets_incoming` UInt64,
    `packets_outgoing` UInt64,
    `bits_incoming` UInt64,
    `bits_outgoing` UInt64,
    `flows_incoming` UInt64,
    `flows_outgoing` UInt64,
    `tcp_packets_incoming` UInt64,
    `tcp_packets_outgoing` UInt64,
    `udp_packets_incoming` UInt64,
    `udp_packets_outgoing` UInt64,
    `icmp_packets_incoming` UInt64,
    `icmp_packets_outgoing` UInt64,
    `fragmented_packets_incoming` UInt64,
    `fragmented_packets_outgoing` UInt64,
    `tcp_syn_packets_incoming` UInt64,
    `tcp_syn_packets_outgoing` UInt64,
    `tcp_bits_incoming` UInt64,
    `tcp_bits_outgoing` UInt64,
    `udp_bits_incoming` UInt64,
    `udp_bits_outgoing` UInt64,
    `icmp_bits_incoming` UInt64,
    `icmp_bits_outgoing` UInt64,
    `fragmented_bits_incoming` UInt64,
    `fragmented_bits_outgoing` UInt64,
    `tcp_syn_bits_incoming` UInt64,
    `tcp_syn_bits_outgoing` UInt64,
    `schema_version` UInt8 DEFAULT 0 COMMENT '2'
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (host, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

ASN metrics schema

Both tables asn_metrics_ipv4 and asn_metrics_ipv6 share same schema:

CREATE TABLE fastnetmon.asn_metrics_ipv4
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `asn_number` UInt64,
    `packets_incoming` UInt64,
    `packets_outgoing` UInt64,
    `bits_incoming` UInt64,
    `bits_outgoing` UInt64,
    `flows_incoming` UInt64,
    `flows_outgoing` UInt64,
    `tcp_packets_incoming` UInt64,
    `tcp_packets_outgoing` UInt64,
    `udp_packets_incoming` UInt64,
    `udp_packets_outgoing` UInt64,
    `icmp_packets_incoming` UInt64,
    `icmp_packets_outgoing` UInt64,
    `fragmented_packets_incoming` UInt64,
    `fragmented_packets_outgoing` UInt64,
    `tcp_syn_packets_incoming` UInt64,
    `tcp_syn_packets_outgoing` UInt64,
    `tcp_bits_incoming` UInt64,
    `tcp_bits_outgoing` UInt64,
    `udp_bits_incoming` UInt64,
    `udp_bits_outgoing` UInt64,
    `icmp_bits_incoming` UInt64,
    `icmp_bits_outgoing` UInt64,
    `fragmented_bits_incoming` UInt64,
    `fragmented_bits_outgoing` UInt64,
    `tcp_syn_bits_incoming` UInt64,
    `tcp_syn_bits_outgoing` UInt64,
    `schema_version` UInt8 DEFAULT 0 COMMENT '2'
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (asn_number, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

System counters schema

System counters table has following schema:

CREATE TABLE fastnetmon.system_metrics
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `name` String,
    `type` String,
    `value` UInt64,
    `schema_version` UInt8 DEFAULT 0 COMMENT '1'
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (name, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

Interface metrics schema

FastNetMon stores per interface bandwidth in table interface_metrics using following schema:

CREATE TABLE fastnetmon.interface_metrics
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `device_ip` String,
    `interface_id` UInt64,
    `interface_name` String,
    `packets_incoming` UInt64,
    `packets_outgoing` UInt64,
    `bits_incoming` UInt64,
    `bits_outgoing` UInt64,
    `flows_incoming` UInt64,
    `flows_outgoing` UInt64,
    `tcp_packets_incoming` UInt64,
    `tcp_packets_outgoing` UInt64,
    `udp_packets_incoming` UInt64,
    `udp_packets_outgoing` UInt64,
    `icmp_packets_incoming` UInt64,
    `icmp_packets_outgoing` UInt64,
    `fragmented_packets_incoming` UInt64,
    `fragmented_packets_outgoing` UInt64,
    `tcp_syn_packets_incoming` UInt64,
    `tcp_syn_packets_outgoing` UInt64,
    `tcp_bits_incoming` UInt64,
    `tcp_bits_outgoing` UInt64,
    `udp_bits_incoming` UInt64,
    `udp_bits_outgoing` UInt64,
    `icmp_bits_incoming` UInt64,
    `icmp_bits_outgoing` UInt64,
    `fragmented_bits_incoming` UInt64,
    `fragmented_bits_outgoing` UInt64,
    `tcp_syn_bits_incoming` UInt64,
    `tcp_syn_bits_outgoing` UInt64,
    `schema_version` UInt8 DEFAULT 0 COMMENT '1'
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (device_ip, interface_id, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

Per hostgroup metrics schema

To store per hostgroup metrics in table total_hostgroup_metrics FastNetMon uses following schema:

CREATE TABLE fastnetmon.total_hostgroup_metrics
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `hostgroup_name` String,
    `packets_incoming` UInt64,
    `packets_outgoing` UInt64,
    `bits_incoming` UInt64,
    `bits_outgoing` UInt64,
    `tcp_packets_incoming` UInt64,
    `tcp_packets_outgoing` UInt64,
    `udp_packets_incoming` UInt64,
    `udp_packets_outgoing` UInt64,
    `icmp_packets_incoming` UInt64,
    `icmp_packets_outgoing` UInt64,
    `fragmented_packets_incoming` UInt64,
    `fragmented_packets_outgoing` UInt64,
    `tcp_syn_packets_incoming` UInt64,
    `tcp_syn_packets_outgoing` UInt64,
    `tcp_bits_incoming` UInt64,
    `tcp_bits_outgoing` UInt64,
    `udp_bits_incoming` UInt64,
    `udp_bits_outgoing` UInt64,
    `icmp_bits_incoming` UInt64,
    `icmp_bits_outgoing` UInt64,
    `fragmented_bits_incoming` UInt64,
    `fragmented_bits_outgoing` UInt64,
    `tcp_syn_bits_incoming` UInt64,
    `tcp_syn_bits_outgoing` UInt64,
    `schema_version` UInt8 DEFAULT 0 COMMENT '1'
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (hostgroup_name, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

Flexible per hostgroup metrics schema

To store flexible counters FastNetMon uses table flexible_total_hostgroup_metrics with following schema:

CREATE TABLE fastnetmon.flexible_total_hostgroup_metrics
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `hostgroup_name` String,
    `schema_version` UInt8 DEFAULT 0 COMMENT '1',
    `memcache_bits_incoming` UInt64,
    `memcache_bits_outgoing` UInt64,
    `memcache_packets_incoming` UInt64,
    `memcache_packets_outgoing` UInt64,
    `chargen_bits_incoming` UInt64,
    `chargen_bits_outgoing` UInt64,
    `chargen_packets_incoming` UInt64,
    `chargen_packets_outgoing` UInt64,
    `dns_bits_incoming` UInt64,
    `dns_bits_outgoing` UInt64,
    `dns_packets_incoming` UInt64,
    `dns_packets_outgoing` UInt64,
    `mt_winbox_bits_incoming` UInt64,
    `mt_winbox_bits_outgoing` UInt64,
    `mt_winbox_packets_incoming` UInt64,
    `mt_winbox_packets_outgoing` UInt64,
    `mt_api_bits_incoming` UInt64,
    `mt_api_bits_outgoing` UInt64,
    `mt_api_packets_incoming` UInt64,
    `mt_api_packets_outgoing` UInt64,
    `ntp_bits_incoming` UInt64,
    `ntp_bits_outgoing` UInt64,
    `ntp_packets_incoming` UInt64,
    `ntp_packets_outgoing` UInt64
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (hostgroup_name, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

Please note that all prefixes for bits_incoming, bits_outgoing, packets_incoming and packets_outgoing are dynamic. They’re created immediately after customer creates hostgroup.

Configuration without flexible traffic counters has only following fields inside:

CREATE TABLE fastnetmon.flexible_total_hostgroup_metrics
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `hostgroup_name` String,
    `schema_version` UInt8 DEFAULT 0 COMMENT '1'
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (hostgroup_name, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

Per host flexible counters

To store flexible counters for hosts we use tables flexible_host_metrics_ipv4 and flexible_host_metrics_ipv6 with following schema:

CREATE TABLE fastnetmon.flexible_host_metrics_ipv6
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `host` String,
    `schema_version` UInt8 DEFAULT 0 COMMENT '1',
    `memcache_bits_incoming` UInt64,
    `memcache_bits_outgoing` UInt64,
    `memcache_packets_incoming` UInt64,
    `memcache_packets_outgoing` UInt64,
    `chargen_bits_incoming` UInt64,
    `chargen_bits_outgoing` UInt64,
    `chargen_packets_incoming` UInt64,
    `chargen_packets_outgoing` UInt64,
    `dns_bits_incoming` UInt64,
    `dns_bits_outgoing` UInt64,
    `dns_packets_incoming` UInt64,
    `dns_packets_outgoing` UInt64,
    `mt_winbox_bits_incoming` UInt64,
    `mt_winbox_bits_outgoing` UInt64,
    `mt_winbox_packets_incoming` UInt64,
    `mt_winbox_packets_outgoing` UInt64,
    `mt_api_bits_incoming` UInt64,
    `mt_api_bits_outgoing` UInt64,
    `mt_api_packets_incoming` UInt64,
    `mt_api_packets_outgoing` UInt64,
    `ntp_bits_incoming` UInt64,
    `ntp_bits_outgoing` UInt64,
    `ntp_packets_incoming` UInt64,
    `ntp_packets_outgoing` UInt64
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (host, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

Attack notifications schema

To implement Grafana notification we use special table attack_events with following schema:

CREATE TABLE fastnetmon.attack_events
(
    `eventDate` Date DEFAULT toDate(eventDateTime),
    `eventDateTime` DateTime,
    `title` String,
    `text` String,
    `event_type` String,
    `ip` String
)
ENGINE = MergeTree
PARTITION BY eventDate
ORDER BY (ip, eventDate)
TTL eventDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

To monitor that Clickhouse export works as expected you can check these counters: 

sudo fcli show system_counters|grep click
clickhouse_metrics_writes_total                            2044855 
clickhouse_metrics_writes_failed                           0

Clickhouse may use significant amount of disk space and you can find detailed guide about ways to control disk space usage by it.

We offer following dashboards which will be installed by our instiller tool automatically.

Also, you could use such query in clickhouse-client tool to retrieve data:

select host, packets_incoming from fastnetmon.host_metrics where metricDate == today() AND metricDateTime >= now() - 5 order by packets_incoming desc limit 10;

If you use existing installation of Clickhouse you need to grant following permissions for Clickhouse user:

SELECT,INSERT,ALTER,CREATE TABLE, CREATE DICTIONARY

Top talkers pre-calculation

To provide lightning fast reports for top talker hosts in network FastNetMon has ability to pre-calculate top 10 hosts which generate most traffic each second and export traffic for them to Clickhouse.

This ability is enabled by default on all new installations. On old installations you can enable it this way:

sudo fcli set main clickhouse_metrics_export_top_hosts true
sudo fcli commit

After enabling this option FastNetMon will create tables:

  • host_metrics_ipv4_top
  • host_metrics_ipv6_top

These tables have schema which is identical to regular tables with traffic about hosts:

CREATE TABLE fastnetmon.host_metrics_ipv4_top
(
    `metricDate` Date DEFAULT toDate(metricDateTime),
    `metricDateTime` DateTime,
    `host` String,
    `packets_incoming` UInt64,
    `packets_outgoing` UInt64,
    `bits_incoming` UInt64,
    `bits_outgoing` UInt64,
    `flows_incoming` UInt64,
    `flows_outgoing` UInt64,
    `tcp_packets_incoming` UInt64,
    `tcp_packets_outgoing` UInt64,
    `udp_packets_incoming` UInt64,
    `udp_packets_outgoing` UInt64,
    `icmp_packets_incoming` UInt64,
    `icmp_packets_outgoing` UInt64,
    `fragmented_packets_incoming` UInt64,
    `fragmented_packets_outgoing` UInt64,
    `tcp_syn_packets_incoming` UInt64,
    `tcp_syn_packets_outgoing` UInt64,
    `tcp_bits_incoming` UInt64,
    `tcp_bits_outgoing` UInt64,
    `udp_bits_incoming` UInt64,
    `udp_bits_outgoing` UInt64,
    `icmp_bits_incoming` UInt64,
    `icmp_bits_outgoing` UInt64,
    `fragmented_bits_incoming` UInt64,
    `fragmented_bits_outgoing` UInt64,
    `tcp_syn_bits_incoming` UInt64,
    `tcp_syn_bits_outgoing` UInt64,
    `schema_version` UInt8 DEFAULT 0 COMMENT '2'
)
ENGINE = MergeTree
PARTITION BY metricDate
ORDER BY (host, metricDate)
TTL metricDate + toIntervalDay(7)
SETTINGS index_granularity = 8192

These tables are very similar to tables used to storing regular per host traffic: host_metrics, host_metrics_ipv6.

In these tables FastNetMon will store top 10 hosts in each particular second. It will use 4 different approaches to calculate top talkers:

  • Top hosts by incoming bytes
  • Top hosts by incoming packets
  • Top hosts by outgoing bytes
  • Top hosts by outgoing packets

If some hosts was picked up as top by more then one approach then we will store it only once.

Clickhouse password reset

In some rare cases you may need to reset password for Clickhouse user used to access database from Grafana:

wget https://install.fastnetmon.com/installer -Oinstaller
sudo chmod +x installer
sudo ./installer -reset_clickhouse_password

Due to Grafana limitations about inability to reload datasource configuration change you may need to run following commands to reload configuration:

sudo apt install -y sqlite3
sudo sqlite3 /var/lib/grafana/grafana.db

And then you will nee to remove cached datasource:

delete from data_source where uid = 'fastnetmonclickhouseofficialdatasourceuid';

And after that restart Grafana:

sudo systemctl restart grafana-server

After all these changes you will be able to access Clickhouse dashboards in Grafana.

24/7 Tech Support

support@fastnetmon.com

Email Us

sales@fastnetmon.com