Measure Port Randomization of Resolvers

Low port randomness of resolvers makes them vulnerable get a poisoned cache (see Kaminsky Attack).
This query is based on DNS OARC presentation by Duane Wessels in 2008.
It estimates, roughly, how random the source port of a resolver is.

A value below 296 is considered as poor. These resolvers are potentially vulnerable to the Kaminsky Attack.

We only measure resolvers from which we have seen more than 100 queries that day.

Link to DNS OARC Slides
Link to Kaminsky Attack

select src, (stddev(srcp) * (count(distinct(srcp)) / count(srcp))) as portrandomization
from dns.queries
where year = 2017 and month = 8 and day = 9
group by src
having portrandomization is not null
and portrandomization < 296
and count(srcp) > 100
order by portrandomization

src, portrandomization
192.168.1.1, 12
10.10.10.10, 15
192.168.7.45 17
...