Technical Tip: How to retrieve active Clickhouse data in FortiSIEM

The following commands below can be used to query Clickhouse data on any FortiSIEM nodes, primarily for monitoring query execution, replication, and partition health.

clickhouse-client

$ SELECT * FROM system.processes; <-- Retrieves all currently running queries/processes in ClickHouse.

$ SELECT * FROM system.query_log ORDER BY event_time DESC LIMIT 10;   <-- Logs all executed SQL queries, sorted with 10 most recent entries.

$ SELECT * FROM system.replication_queue format Vertical;  <-- Lists pending replication tasks in a more readable vertical format.

$ SELECT * from system.replicas format Vertical;  <-- Shows the status of all replicas in the system.

If the entries show is_readonly = 1 or is_session_expired = 1, the replica may be unhealthy or disconnected.

$ SELECT * FROM system.parts WHERE active = 1;  <-- Lists all active data parts in tables, it can also refer for troubleshooting inconsistent data or missing parts.