Parsing Zeek JSON with Clickhouse Local

clickhouse-local-zeek.md

Refs

• See https://clickhouse.com/docs/en/operations/utilities/clickhouse-local

Conn.log

mfranz@opti3070:~/zeek/2024-12-14$ clickhouse local -q "select count(*) as cnt, id.orig_h from 'conn.*.log' group by id.orig_h order by cnt desc limit 50"
111901  192.168.2.167
40425   192.168.3.109
35433   192.168.3.135
9835    192.168.3.133
7402    192.168.3.215
5446    192.168.2.134
3809    192.168.3.168
2479    192.168.2.178
1859    192.168.2.165
1548    192.168.2.179
1489    192.168.4.50
751     192.168.3.241
451     192.168.4.196
405     fe80::1479:c3c7:e306:2b6b
371     192.168.2.214
283     fe80::143e:3083:40cf:d43d
151     192.168.2.129
149     192.168.2.172
98      fe80::18ee:f37f:b14:f66b
92      0.0.0.0
77      fe80::a048:180c:41de:cfbe
69      192.168.3.175
64      192.168.3.228
61      fe80::8647:9ff:fe19:c316
61      192.168.2.176
52      173.64.108.157
46      192.168.2.1
46      192.168.2.100
42      192.168.2.166
26      192.168.2.173
26      192.168.2.139
18      fe80::ba6f:541b:22c0:767f
16      192.168.3.1
15      fe80::a3c:40cf:5294:66d
14      192.168.2.220
13      fe80::5ef7:e6ff:fe8b:e8a3
12      fe80::2c7d:2fff:fe3a:f646
12      fe80::dea6:32ff:fe39:6f6d
12      fe80::dea6:32ff:fef7:1db7
12      fe80::5054:ff:fefd:2579
12      fe80::5054:ff:fe06:66bf
12      fe80::5054:ff:fe2e:8297
12      fe80::dea6:32ff:feb1:dc85
12      fe80::d65d:64ff:fed1:d007
12      fe80::18c9:3eff:fe44:8d25
12      fe80::9876:59ff:feec:bf19
12      fe80::5054:ff:fe2b:2c1c
11      fe80::78f4:3dff:fe9e:3add
11      fe80::2ecf:67ff:fe41:a54f
9       35.211.202.130

SSL Log

 clickhouse local -q "select count(*) as cnt, server_name from 'ssl.*.log' group by server_name order by cnt desc limit 50"
2344    \N
459     mask.icloud.com
421     dns.google
402     aws.api.snapchat.com
325     bolt-gcdn.sc-cdn.net
321     gateway.icloud.com
255     app-analytics-v2.snapchat.com
242     gcs.sc-cdn.net
225     aws-proxy-gcp.api.snapchat.com
181     m.media-amazon.com
138     cf-st.sc-cdn.net
123     hub.windmill.dev
110     gcp.api.snapchat.com
103     flux-c.sc-cdn.net
99      tpsc-ue1.doubleverify.com
93      assets.layer.ea.com
91      bag.itunes.apple.com
79      usc1-gcp-v62.api.snapchat.com
75      play.googleapis.com
71      login.microsoftonline.com
69      settings-win.data.microsoft.com
65      xblgdvrassets2012.blob.core.windows.net
62      titlestorage.xboxlive.com
60      graph.facebook.com
60      longhorn-upgrade-responder.rancher.io
59      www.google-analytics.com
59      www.googletagmanager.com
58      app-analytics-services.com
56      sc-static.net
55      eaassets-a.akamaihd.net
54      www.google.com
52      gdmf.apple.com
51      aax-us-east.amazon-adsystem.com
51      i.instagram.com
50      app-site-association.cdn-apple.com
48      sts.amazonaws.com
48      ec2.us-east-1.amazonaws.com
46      oauthaccountmanager.googleapis.com
43      s.amazon-adsystem.com
42      consumer-mobile-bff.doordash.com
41      inbox.google.com
40      graph.instagram.com
40      instagram.fagc3-2.fna.fbcdn.net
38      www.googleapis.com
38      iguazu.doordash.com
38      tr.snapchat.com
37      proxy-safebrowsing.googleapis.com
37      iphone-ld.apple.com
37      scontent-iad3-2.cdninstagram.com
37      www.amazon.com

Have you checked out chdb? It’s pretty awesome too. https://github.com/chdb-io/chdb

I saw it a few months back, thanks for the reminder!