Skip to main content
Version: 23.04

Clickhouse

Before starting​

  • In most cases, you will want to send data from the central server. It is also possible to send it from a remote server or a poller (e.g. if you want to avoid the central server being a SPOF, or if you are an MSP and you install the stream connector on a poller or a remote server within your customer's infrastructure).
  • By default, the Clickhouse stream connector sends data from host_status and service_status Broker events. These metrics are contained in the perf_data field of the events. The event format is shown here.
  • These events are sent each time a host or a service is checked. Various parameters let you filter out events.

Prerequisites​

  • The Clickhouse HTTP interface must be enabled. (Clickhouse Documentation).
  • You must have a valid user/password that can INSERT data into the desired table.
  • You must create a table in Clickhouse to receive Centreon data. Here is the table schema (you can change the database and table name. They are both configurable in the stream connector configuration).

Standard table schema​

This is the default schema that should be used.

CREATE TABLE centreon_stream.metrics
(
host String,
service String,
metric_id String,
metric_name String,
metric_unit String,
metric_value Decimal,
metric_min Decimal,
metric_max Decimal,
timestamp DateTime,
hostgroups Array(String)
)
ENGINE = MergeTree()
PRIMARY KEY (timestamp, host, service, metric_name, metric_id)

Alternative table schema​

Disclaimer: you should not use this schema unless you absolutely want the internal metric_id of Centreon. This comes with the loss of many possibilities such as having access to metric units, min, max.... To use this schema, please refer to the use_deprecated_metric_system parameter documentation below.

CREATE TABLE centreon_stream.metrics
(
host String,
service String,
metric_id BIGINT,
metric_name String,
metric_value Decimal,
timestamp DateTime,
hostgroups Array(String)
)
ENGINE = MergeTree()
PRIMARY KEY (timestamp, host, service, metric_name, metric_id)

Installation​

Perform the installation on the server that will send data to Clickhouse (central server, remote server, poller).

  1. Login as root using your favorite SSH client.

  2. Install the Epel repository.

dnf install epel-release
  1. Install the Centreon lua modules.
dnf install centreon-stream-connectors-lib
  1. Download the Clickhouse metrics stream connector:
wget -O /usr/share/centreon-broker/lua/clickhouse-metrics-apiv2.lua https://raw.githubusercontent.com/centreon/centreon-stream-connector-scripts/develop/centreon-certified/clickhouse/clickhouse-metrics-apiv2.lua
chmod 644 /usr/share/centreon-broker/lua/clickhouse-metrics-apiv2.lua

Configuring your Clickhouse equipment​

You may need to configure your Clickhouse equipment so that it can receive data from Centreon. Please refer to Clickhouse's documentation. Make sure Clickhouse is able to receive data sent by Centreon: flows must not be blocked by Clickhouse's configuration or by a security equipment.

Configuring the stream connector in Centreon​

  1. On your central server, go to Configuration > Pollers > Broker configuration.
  2. Click on central-broker-master (or the appropriate broker configuration if it is a poller or a remote server that will send events).
  3. On the Output tab, select Generic - Stream connector from the list and then click Add. A new output appears in the list.
  4. Fill in the fields as follows:
FieldValue
NameClickhouse metrics
Path/usr/share/centreon-broker/lua/clickhouse-metrics-apiv2.lua
Filter categoryNeb
  1. To enable Centreon to connect to your Clickhouse equipment, fill in the following mandatory parameters. The fields for the first entry are already present. Click on the +Add a new entry link located below the Filter category table to add another one.
TypeNameValue explanationValue exemple
stringuserClickhouse user that will be usedcentreon
stringpasswordPassword for this usercentreon
stringhttp_server_urlAddress of the Clickhouse server (include the protocol and the port)https://myclickhouse.local:8123
  1. Fill in any optional parameters you want (using the +Add a new entry link):
TypeNameValue explanationdefault value
stringclickhouse_databaseName of the database in which the desired table is storedcentreon_stream
stringclickhouse_tableTable in which metrics are writtenmetrics
numberuse_deprecated_metric_systemAllows you to use the alternative table schema when set to 10
  1. Use the stream connector's optional parameters to filter or adapt the data you want Centreon to send to Clickhouse.

  2. Deploy the configuration.

  3. Restart centengine on all pollers:

    systemctl restart centengine

    Clickhouse should now receive data from Centreon. To test if it is working, see Curl commands: testing the stream connector.

Filtering or adapting the data you want to send to Clickhouse​

All stream connectors have a set of optional parameters, that allow you to filter the data you will send to your Clickhouse equipment, to reformat the data, to define a proxy...

Each optional parameter has a default value, that is indicated in the corresponding documentation.

  • To override the default value of a parameter, click the +Add a new entry link located below the Filter category table to add a custom parameter. For example, if you want to only send to Clickhouse the events handled by a poller named "poller-1", enter:

    type = string
    name = accepted_pollers
    value = poller-1
  • For the Clickhouse stream connector, the following values always override the default values, you do not need to define them in the interface.

TypeNameDefault value for the stream connectorAdditional notes
stringaccepted_categoriesneb
stringaccepted_elementshost_status,service_status
stringmax_buffer_size1000You should lower this value to about a hundred if you have less than 10 000 services. If you are performing troubleshooting operations with log_curl_commands and/or send_data_test parameters, you can lower this value to less than 10.
numberhard_only0
numberenable_host_status_dedup0
numberenable_service_status_dedup0

Event bulking​

This stream connector is compatible with event bulking: it is able to send more that one event in each call to the Clickhouse REST API.

To use this feature you must add the max_buffer_size parameter in your stream connector configuration.

Event format​

This stream connector will send event with the following format.

service_status event​

INSERT INTO centreon_stream.metrics (host, timestamp, metric_name, metric_value, service, hostgroups, metric_id, metric_unit, metric_min, metric_max) VALUES ('central_1',1702910747,'rtmin',0.005,'Ping',['hg'],'10-8-rtmin','ms',,),('central_1',1702910747,'rta',0.061,'Ping',['hg'],'10-8-rta','ms',0.0,),('central_1',1702910747,'pl',0.0,'Ping',['hg'],'10-8-pl','%',0.0,100.0)

host_status event​

INSERT INTO centreon_stream.metrics (host, timestamp, metric_name, metric_value, service, hostgroups, metric_id, metric_unit, metric_min, metric_max) VALUES ('central_3',1702910932,'rtmin',0.0,'Ping',['hg'],'12-10-rtmin','ms',,),('central_3',1702910932,'rta',0.0,'Ping',['hg'],'12-10-rta','ms',0.0,),('central_3',1702910932,'pl',100.0,'Ping',['hg'],'12-10-pl','%',0.0,100.0)

Results inside Clickhouse​

β”Œβ”€host────┬─service─┬─metric_name─┬─metric_unit─┬─metric_value─┬─metric_min─┬─metric_max─┬───────────timestamp─┬─hostgroups─────────────┐
β”‚ central β”‚ β”‚ pl β”‚ % β”‚ 0 β”‚ 0 β”‚ 100 β”‚ 2023-11-27 14:23:31 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ β”‚ rta β”‚ ms β”‚ 0.052 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:23:31 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ β”‚ rtmax β”‚ ms β”‚ 0.052 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:23:31 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ β”‚ rtmin β”‚ ms β”‚ 0.052 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:23:31 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ Ping β”‚ pl β”‚ % β”‚ 0 β”‚ 0 β”‚ 100 β”‚ 2023-11-27 14:26:51 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ Ping β”‚ pl β”‚ % β”‚ 0 β”‚ 0 β”‚ 100 β”‚ 2023-11-27 14:26:51 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ Ping β”‚ rta β”‚ ms β”‚ 0.013 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:26:51 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ Ping β”‚ rta β”‚ ms β”‚ 0.013 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:26:51 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ Ping β”‚ rtmax β”‚ ms β”‚ 0.049 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:26:51 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ Ping β”‚ rtmax β”‚ ms β”‚ 0.049 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:26:51 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ Ping β”‚ rtmin β”‚ ms β”‚ 0.004 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:26:51 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ Ping β”‚ rtmin β”‚ ms β”‚ 0.004 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:26:51 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ β”‚ pl β”‚ % β”‚ 0 β”‚ 0 β”‚ 100 β”‚ 2023-11-27 14:28:11 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ β”‚ rta β”‚ ms β”‚ 0.027 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:28:11 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ β”‚ rtmax β”‚ ms β”‚ 0.027 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:28:11 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β”‚ central β”‚ β”‚ rtmin β”‚ ms β”‚ 0.027 β”‚ 0 β”‚ 0 β”‚ 2023-11-27 14:28:11 β”‚ ['hg_1','hg_2','hg_3'] β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”΄β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

Custom event format​

You can't change the format of the event for metrics oriented stream connectors. This means you cannot send other Broker events that contain performance data.

Curl commands: testing the stream connector​

Sending events​

If you want to test that events are sent to Clickhouse correctly:

  1. Log in to the server that you configured to send events to Clickhouse (your central server, a remote server or a poller).
  2. Run the following command:
curl -X POST -H 'X-ClickHouse-User: <user>' -H 'X-ClickHouse-Key: <password>' '<http_server_url>' -d 'INSERT INTO <clickhouse_database>.<clickhouse_table> (host, timestamp, metric_name, metric_value, service, hostgroups, metric_id, metric_unit, metric_min, metric_max) VALUES ('central_2',1702910872,'rtmin',0.0,'Ping',['hg'],'11-9-rtmin','ms',,),('central_2',1702910872,'rta',0.0,'Ping',['hg'],'11-9-rta','ms',0.0,),('central_2',1702910872,'pl',100.0,'Ping',['hg'],'11-9-pl','%',0.0,100.0)'

You must replace all the <xxxx> inside the below commands with their appropriate value. For example, <clickhouse_database> may become centreon_stream.

  1. Check that the event has been received by Clickhouse.