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
|
select qname as CertainQnames, qtype as Qtype, count(1) as count from dns where (qname='localhost' or qname like '%.root-servers.net') and qr==0 group by CertainQnames,Qtype order by count desc;
select qtype as Qtype, qname as Qname, count(1) as count from dns where qclass==3 and qr==0 group by Qtype,Qname order by count desc;
select rcode as Rcode, if(qr==1,dst_addr,src_addr) as ClientAddr, count(1) as count from dns where qr==1 group by Rcode,ClientAddr order by count,Rcode,ClientAddr desc limit 50;
select 'ALL' as All, if(ether_type==34525,rsplit(src_addr,7,':')||':'||rsplit(src_addr,6,':')||':'||rsplit(src_addr,5,':')||':'||rsplit(src_addr,4,':')||':'||rsplit(src_addr,3,':')||'::',rsplit(src_addr,3)||'.'||rsplit(src_addr,2)||'.'||rsplit(src_addr,1)||'.0') as ClientSubnet, count(1) as count from dns where qr==0 group by All,ClientSubnet order by count,ClientSubnet desc limit 200;
#select 'ALL' as All, subnet(src_addr,24,96) as ClientSubnet, count(1) as count from dns where qr==0 group by All,ClientSubnet order by count desc,ClientSubnet limit 200;
select if(rsplit(qname,1)='de','ok','non-auth-tld') as Class, if(ether_type==34525,rsplit(src_addr,7,':')||':'||rsplit(src_addr,6,':')||':'||rsplit(src_addr,5,':')||':'||rsplit(src_addr,4,':')||':'||rsplit(src_addr,3,':')||'::',rsplit(src_addr,3)||'.'||rsplit(src_addr,2)||'.'||rsplit(src_addr,1)||'.0') as ClientSubnet, count(1) as count from dns where qr==0 group by Class,ClientSubnet order by count,ClientSubnet,Class desc limit 200;
select if(qr==1,'sent','recv') as Direction, if(protocol==6,'tcp',if(protocol==17,'udp',if(protocol==1,'icmp',if(protocol==58,'ipv6-icmp',protocol)))) as IPProto, count(1) as count from dns group by Direction,IPProto order by count,Direction,IPProto desc;
select if(ether_type==34525,'IPv6','IPv4') as IPVersion, qtype as Qtype, count(1) as count from dns where qr==0 group by IPVersion,Qtype order by count,IPVersion,Qtype desc;
select 'ALL' as All, do, edns0, edns_version, extended_rcode, z, if(do==1,'set','clr') as D0, count(1) as count from dns where qr==0 group by All,do,D0,edns0,edns_version,extended_rcode,z order by count desc;
select 'ALL' as All, if(edns0,edns_version,'none') as EDNSVersion, count(1) as count from dns where qr==0 group by All,EDNSVersion order by count desc;
select 'ALL' as All, if(qname like 'xn--%','idn','normal') as IDNQname, count(1) as count from dns where qr==0 group by All,IDNQname order by count desc;
select 'ALL' as All, lower(rsplit(qname,1)) as TLD, count(1) as count from dns where qr==0 and (qname like 'xn--%') group by All,TLD order by count,TLD desc;
select 'ALL' as All, if(qr==1,dst_addr,src_addr) as ClientAddr, count(1) as count from dns where qr==0 and (qtype=28 or qtype=38) and (qname like '%.root-servers.net') group by All,ClientAddr order by count desc limit 50;
select 'ALL' as All, opcode as Opcode, count(1) as count from dns where qr==0 group by All,Opcode order by count,Opcode desc;
select 'ALL' as All, qtype as Qtype, count(1) as count from dns where qr==0 group by All,Qtype order by count,Qtype desc;
select qtype as Qtype, len(qname) as QnameLen, count(1) as count from dns where qr==0 group by Qtype,QnameLen order by count,QnameLen,Qtype desc;
select qtype as Qtype, lower(rsplit(qname,1)) as TLD, count(1) as count from dns where qr==0 and (qtype=1 or qtype=2 or qtype=5 or qtype=6 or qtype=12 or qtype=15 or qtype=28 or qtype=38 or qtype=255) group by Qtype,TLD order by count,TLD,Qtype desc limit 200;
select 'ALL' as All, rcode as Rcode, count(1) as count from dns where qr==1 group by All,Rcode order by count,Rcode desc;
select rcode as Rcode, msg_size as ReplyLen, count(1) as count from dns where qr==1 group by Rcode,ReplyLen order by count,Rcode,ReplyLen desc;
select 'ALL' as All, rd as RD, count(1) as count from dns where qr==0 group by All,RD order by count desc;
select if(protocol==6,'tcp',if(protocol==17,'udp',protocol)) as Transport, qtype as Qtype, count(1) as count from dns where qr==0 group by Transport,Qtype order by Transport,Qtype,count desc;
select s, dst_addr as Dst_addr, qtype as questiontype, lower(src_addr) as lower_src, if(1 and s < 1 or s <= 1 or s > 1 or s >= 1, 't', 'f'), trim(trim('foofoo' || rsplit(src_addr, 1) || 'foofoo', 'foo'), 'bar'), count(*), len(src_addr), sum(msg_size + -1 - 2 % 4 << 3 >> 2 | 3 & ~4) + 1, min(msg_size), max(msg_size), truncate(1.1) as integer, 1.1 as float, sum(src_port + 1.0 - 2.0 / 1.5 * -2.5) + 1.0, max(src_port + 1.0), min(src_port + 1.0), avg(src_port), stdev(src_port), name('rcode', 0) from dns where src_addr like '%' and (qr or not qr) group by src_addr, s having s >= 0 order by s, dst_addr, lower_src, integer, float;
select name( 'qtype' , qtype ) as qt, count(*) as count from dns group by qtype order by count, qt desc;
#select count(*) as count, lower(rsplit(qname,1)) as tld, istld(tld) as flag from dns group by tld order by count desc limit 50;
select * from icmp;
select count(*) from icmp;
|
