Total number of resolvers over the past week:
select count(distinct src) as n from dns.queries
where server in ('a.nic.ch','b.nic.ch') and
( (year = 2017 and month = 9 and day = 19) or (year = 2017 and month = 9 and day = 18) or (year = 2017 and month = 9 and day = 17) or (year = 2017 and month = 9 and day = 16) or (year = 2017 and month = 9 and day = 15) or (year = 2017 and month = 9 and day = 14) or (year = 2017 and month = 9 and day = 13) )
Resolvers per domain over the past week:
select domainname, count(distinct src) as n from dns.queries
where server in ('a.nic.ch','b.nic.ch') and rcode != 3 and
( (year = 2017 and month = 9 and day = 19) or (year = 2017 and month = 9 and day = 18) or (year = 2017 and month = 9 and day = 17) or (year = 2017 and month = 9 and day = 16) or (year = 2017 and month = 9 and day = 15) or (year = 2017 and month = 9 and day = 14) or (year = 2017 and month = 9 and day = 13) )
group by domainname order by n desc
Resolvers per 'web domain' over the past week:
select domainname, count(distinct src) as n from dns.queries
where server in ('a.nic.ch','b.nic.ch') and rcode != 3 and (qtype = 1 or qtype = 28) and (domainname = qname or qname like 'www.%') and
( (year = 2017 and month = 9 and day = 19) or (year = 2017 and month = 9 and day = 18) or (year = 2017 and month = 9 and day = 17) or (year = 2017 and month = 9 and day = 16) or (year = 2017 and month = 9 and day = 15) or (year = 2017 and month = 9 and day = 14) or (year = 2017 and month = 9 and day = 13) )
group by domainname order by n desc