clickhouse¶

Giới thiệu ClickHouse¶

ClickHouse là hệ quản trị cơ sở dữ liệu dạng cột (column-oriented DBMS) mã nguồn mở, được phát triển bởi Yandex.
Mục tiêu của nó là xử lý truy vấn phân tích (OLAP) với hiệu năng cực cao trên lượng dữ liệu rất lớn (hàng tỷ dòng).

1.1 Đặc điểm nổi bật:¶

Lưu trữ theo cột: tăng tốc độ truy vấn khi chỉ cần đọc một số cột thay vì toàn bộ bản ghi.
Hiệu năng cao: có thể xử lý hàng tỷ bản ghi trong vài giây.
Nén dữ liệu mạnh: giảm đáng kể dung lượng lưu trữ.
Phân tán (Distributed): hỗ trợ cluster, replication, sharding.
Hỗ trợ SQL chuẩn: dễ sử dụng cho người quen với SQL
Thích hợp cho: log analytics, metrics, event data, SOC telemetry, SIEM backend.

Truy vấn Clickhouse trên Portal¶

Giúp người dùng có thể dễ dàng truy vấn câu lệnh clickhouse trên portal. Có thể truy vấn tập trung nhiều tổ chức khác nhạu.

2.1 Các bảng hỗ trợ¶

Column	Description
alerts_01	Lưu toàn bộ log alert.json sang Clickhouse
archives_01	Lưu toàn bộ log của archives.json sang Clickhouse
flows	Lưu luồng dữ liệu netflows vào Clickhouse
xmons	Lưu log Entity and Behavior Analytics (UEBA) trên windows vào Clickhouse

Để biết chi tiết các field của bảng truy vấn theo câu lệnh:
Desc <Tên table>;

Ví dụ: Desc alerts_01

2.2 Các trường hợp sử dụng Clickhouse¶

Vào Menu chọn Hunting
Chọn Raw Query
Chọn tổ chức
Chọn sensor tương ứng
Viết câu lệnh truy vấn

Dưới đây là một số trường hợp cụ thể khi sử dụng ClickHouse, có thể tùy chỉnh linh hoạt theo nhu cầu thực tế.

2.2.1 Thống kê số lượng bản ghi alert trong 7 ngày¶

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 Thống kê số lượng bản ghi flows trong 7 ngày¶

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 Thống kê băng thông sử dụng trong 7 ngày¶

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 Tổng lưu lượng vào/ra theo 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 địa chỉ nguồn tạo nhiều luồng nhất¶

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 địa chỉ đích nhận nhiều luồng nhất¶

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 giao thức ứng dụng được sử dụng nhiều nhất¶

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 Thống kê mã lỗi HTTP (từ 400 trở lên)¶

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 Use case Clickhouse¶

2.3.1 Case FodHelper.exe¶

Đây là một event có liên quan đến tấn công nâng quyền, để làm rõ event này chúng tôi cần tìm kiếm thêm thông tin để xác nhận
Chúng tôi thấy process có tên là FodHelper.exe, chúng tôi sẽ tìm kiếm trên toàn hệ thống xem đã có bao nhiêu lần event liên quan đến process này

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

Có 5 event liên quan đến FodHelper.exe

Chúng tôi có thể kiểm tra khoảng thời gian xuất hiện của event

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

Event xuất hiện từ ngày 10 - 16 tháng 11

Event liên quan tới FodHelper.exe thì không thể quên việc kiểm tra "ms-settings", (tham khảo: https://gist.github.com/netbiosX/a114f8822eb20b115e33db55deee6692)
Chúng tôi sẽ tìm xem toàn bộ event có tồn tại dữ liệu liên quan tới "ms-settings” không

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

Chúng tôi không thấy event nào liên quan tới "ms-settings”

Tới đây chúng tôi có thể kết luận không có cuộc tấn công nâng quyền nào thông qua FodHelper.exe

Nếu với các bạn có kinh nghiệm hơn thì lần đầu nhìn event đã biết được đây là cuộc tấn công nâng quyền hay không

Chúng tôi có thể thấy event thực thi bởi người dùng SYSTEM, đây không phải là hành vi nâng quyền (vì trường hợp nâng quyền là người dùng thông thường)

2.3.2 Case Winos4.0 CnC¶

Trong quá trình giám sát chúng tôi phát hiện được một thiết bị trong mạng đang kết nối với một ip lạ, có cánh bảo là C2 server

Chúng tôi thử tìm kiếm trên virustotal thì ip không có dấu hiệu độc hại nào

Chúng tôi tiến hành điều tra thêm những thiết bị đã từng kết nối tới ip này

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

Trong đó các địa chỉ thuộc dải 172.31 là những truy cập cần chú ý (các ip khác là thiết bị của tier01 đang hunting)

Chúng tôi xác định ip 172.31.98.140 thiết bị gì, có truy cấp đến những ứng dụng nào

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

Chúng tôi xác định đây là một thiết bị android đang kết nối thông qua mạng wifi của công ty
Tới đây chúng tôi chỉ có thể nhờ bộ phận IT và quản lý thiết bị của công ty xem đây là thiết bị nào, ai đang sử dụng nó

Cuối cùng chúng tôi cũng tìm ra thiết bị này, và chúng tôi đã đưa thiết bị vào danh sách theo dõi thêm

2.3.3 Case Zabbix traffic¶

Một case Malware khác

Chúng tôi điều tra ip 103.28.37.59

order by count() desc```



Chúng tôi thấy những giao thức chủ yếu được sử dụng là Zabbix
Theo như thông tin từ bộ phận IT cung cấp thì Zabbix đang được cài trên các máy tính này để theo dõi sức khỏe

Chúng tôi kết luận Cảnh báo trên là vô hại

## 3. Tham khảo
https://clickhouse.com/docs