DNS Magnitude

Impala Query:

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

Domain Names

DNS Magnitude

Description:

Impala Query:

Example Output: