Technical Tip: Clickhouse data review per day

Description

This article describes a way to review the accumulated data per day when Clickhouse is in use.

Scope

FortiSIEM with Clickhouse.

Solution

In the GUI, data can be viewed under Admin -> Settings -> Database -> Online data.

To query the same data from the CLI:

SELECT
    toDate(min_time) AS event_date,
    disk_name,
    round (formatReadableSize(SUM(bytes_on_disk) ) , 2) AS GB_per_day,
    count() AS part_count
FROM system.parts
WHERE (database = 'fsiem')
  AND (table = 'events_replicated')
  AND active
GROUP BY event_date, disk_name
ORDER BY event_date ASC, disk_name ASC;

A query that can be executed from the CLI with a slight modification of the shown data.

clickhouse-client --query "SELECT toDate(phRecvTime) AS day, formatReadableSize(sum(rawEventSize)) AS original_log_volume, sum(rawEventSize) AS original_log_bytes, count() AS events FROM fsiem.events_replicated GROUP BY day ORDER BY day;"