Clickhouse¶

Introduction to ClickHouse¶

ClickHouse is an open-source column-oriented DBMS (column-oriented database management system) developed by Yandex.
Its goal is to process analytical queries (OLAP) with extremely high performance on very large data volumes (billions of rows).

1.1 Key Features:¶

Columnar storage: speeds up queries when only specific columns need to be read instead of entire records.
High performance: can process billions of records in seconds.
Strong data compression: significantly reduces storage capacity.
Distributed: supports cluster, replication, sharding.
Standard SQL support: easy for SQL-familiar users
Suitable for: log analytics, metrics, event data, SOC telemetry, SIEM backend.

Querying ClickHouse on Portal¶

Allows users to easily query ClickHouse commands on the portal. Can query multiple different organizations centrally.

2.1 Supported Tables¶

Column	Description
alerts_01	Stores all alert.json logs to ClickHouse
archives_01	Stores all archives.json logs to ClickHouse
flows	Stores netflows data into ClickHouse
xmons	Stores UEBA (Entity and Behavior Analytics) logs on Windows into ClickHouse

To know details of table fields, query by command:
Desc <Table Name>;

Example: Desc alerts_01

2.2 Use Cases for ClickHouse¶

Go to Menu select Hunting
Select Raw Query
Select organization
Select corresponding sensor
Write query command

Below are specific use cases when using ClickHouse, which can be flexibly customized according to actual needs.

2.2.1 Statistics of alert record count in 7 days¶

SELECT 
    formatDateTime(timestamp, '%Y-%m-%d') AS date,
    count() AS record_count
FROM SIEM.alerts_01
WHERE timestamp >= today() - 7
GROUP BY date
ORDER BY date DESC;

2.2.2 Statistics of flows record count in 7 days¶

SELECT 
    formatDateTime(_date, '%Y-%m-%d') AS date,
    count() AS record_count
FROM SIEM.flows
WHERE _date >= today() - 7
GROUP BY date
ORDER BY date DESC;

2.2.3 Bandwidth usage statistics in 7 days¶

SELECT 
    formatDateTime(_date, '%Y-%m-%d') AS date,
    round( sum(IN_BYTES)  / 1024 / 1024 / 1024 , 2) AS total_in_Gbytes,
    round( sum(OUT_BYTES)  / 1024 / 1024 / 1024 ,2 )  AS total_out_Gbytes,
    round( sum(IN_BYTES + OUT_BYTES) / 1024 / 1024 / 1024 ,2 ) AS total_bandwidth_GB
FROM SIEM.flows
WHERE _date >= today() - 7
GROUP BY _date
ORDER BY _date DESC;

2.2.4 Total inbound/outbound traffic by interface¶

SELECT 
    INTERFACE, 
    sum(IN_BYTES) AS TOTAL_IN_BYTES, 
    sum(OUT_BYTES) AS TOTAL_OUT_BYTES 
FROM flows 
GROUP BY INTERFACE;

2.2.5 Top 10 source addresses creating most flows¶

SELECT IPV4_SRC_ADDR, count(*) AS FLOW_COUNT 
FROM flows 
GROUP BY IPV4_SRC_ADDR 
ORDER BY FLOW_COUNT DESC 
LIMIT 10;

2.2.6 Top 10 destination addresses receiving most flows¶

SELECT IPV4_DST_ADDR, count(*) AS FLOW_COUNT 
FROM flows 
GROUP BY IPV4_DST_ADDR 
ORDER BY FLOW_COUNT DESC 
LIMIT 10;

2.2.7 Top 10 most used application protocols¶

SELECT L7_PROTO_NAME, count(*) AS FLOW_COUNT 
FROM flows 
GROUP BY L7_PROTO_NAME 
ORDER BY FLOW_COUNT DESC 
LIMIT 10;

2.2.8 HTTP error code statistics (from 400 and above)¶

SELECT HTTP_RET_CODE, count(*) AS ERROR_COUNT 
FROM flows 
WHERE HTTP_RET_CODE >= 400 
GROUP BY HTTP_RET_CODE 
ORDER BY ERROR_COUNT DESC;

2.3 ClickHouse Use Cases¶

2.3.1 FodHelper.exe Case¶

This is an event related to privilege escalation attack, to clarify this event we need to search for more information to confirm
We see the process named FodHelper.exe, we will search across the entire system to see how many events related to this process

select count() from archives_01 where full_log like `%FodHelper.exe%`

There are 5 events related to FodHelper.exe

We can check the time period of event occurrence

select timestamp from archives_01 where full_log like '%FodHelper.exe%' order by timestamp

Event appeared from November 10-16

Events related to FodHelper.exe cannot forget checking "ms-settings", (reference: https://gist.github.com/netbiosX/a114f8822eb20b115e33db55deee6692)
We will search if all events have data related to "ms-settings"

select * from archives_01 where full_log like '%ms-settings%'

We found no events related to "ms-settings"

At this point we can conclude there was no privilege escalation attack through FodHelper.exe

If you have more experience, at first glance at the event you can tell if it's a privilege escalation attack or not

We can see the event was executed by SYSTEM user, this is not a privilege escalation behavior (because privilege escalation case is a normal user)

2.3.2 Winos4.0 CnC Case¶

During monitoring we detected a device in the network connecting to an unfamiliar IP, rumored to be a C2 server

We tried searching on virustotal but the IP had no malicious indicators

We proceeded to investigate other devices that had connected to this IP

select count() as count, IPV4_SRC_ADDR from flows where IPV4_DST_ADDR=171.244.128.56' group by IPV__SRC_ADDR order by count desc

Among them, addresses in the 172.31 range are accesses that need attention (other IPs are tier01 devices hunting)

We identified what device 172.31.98.140 is, what applications it accessed

select count(), IN_SRC_MAC,L7_PROTO_NAME from flows where IPV4_SRC_ADDR='172.31.98.140' group by IN_SRC_MAC, L7_PROTO_NAME,DNS_QUERY
order by count() desc

select count(), IN_SRC_MAC,L7_PROTO_NAME,DNS_QUERY from flows where IPV4_SRC_ADDR='172.31.98.140' group by IN_SRC_MAC, L7_PROTO_NAME,DNS_QUERY
order by DNS_QUERY desc

We identified this as an android device connecting through the company wifi
At this point we can only rely on IT department and device management to identify this device, who is using it

Finally we found this device, and we added the device to the watch list

2.3.3 Zabbix Traffic Case¶

Another Malware case

We investigated IP 103.28.37.59

select order by count() desc

We see the main protocols used are Zabbix According to information from IT department, Zabbix is installed on these computers for health monitoring

We conclude the above alert is harmless

3. Reference¶

https://clickhouse.com/docs