example_queries.sql 1.35 KB
Newer Older
Alexander Withers's avatar
Alexander Withers committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37


 lots of good example from bro-clickhouse package:
 https://github.com/ncsa/bro-clickhouse/tree/master/example_queries

 common queries:

select count(*) from sshauth;
select username, password, uniq(src) as sources from sshauth group by usernane, passsword order by sources desc limit 20

 top 10 attackers:

select src,count(src) cnt from sshauth group by src order by cnt DESC limit 10;

select password,count(password) cnt from sshauth group by password order by cnt DESC limit 10;
select duser,count(duser) cnt from sshauth group by duser order by cnt DESC limit 10;

 top 10 ssh clients:

select client_version,count(client_version) cnt from sshauth group by client_version order by cnt DESC limit 10;

 least common clients:

select client_version,count(client_version) cnt from sshauth group by client_version order by cnt ASC limit 20;

select password,count(password) cnt from sshauth group by password order by cnt ASC limit 20;

 others:

select client_version,src,count() cnt from sshauth where msg != 'Connection' group by src,client_version order by cnt desc limit 5 by src limit 100;


-- you can pipe this into clickhouse:
-- echo "select client_version,src,count() cnt from \
--       sshauth where msg != 'Connection' \
--       group by src,client_version \
--       order by cnt desc limit 5 by src limit 100;" | clickhouse-client | less