Query examples

The ENTRADA data model support many types of SQL queries which can be used to a extract interesting information from the captured network data. You might be interested in resolver behaviour, domain name usage or the use of certain types of DNS queries. Here we display some example queries, below each query, we show its (fictional) result.

Count daily queries for one domain

This query counts daily DNS queries that are stored for a single domain. In this case, we count the queries for December 2015. The built in function from_unixtime converts a unixtime to a human readable format. If you want to know the total number of daily queries, then just leave out the domainname condition.

select from_unixtime(unixtime,"yyyy-MM-dd") as time, count(1) as tot
from dns.queries 
where year = 2015 and month=12
and domainname = "example.nl"
group by from_unixtime(unixtime,"yyyy-MM-dd")
order by time

This statement generates the following output:

time tot
2015-12-01 512
2015-12-02 645
2015-12-03 630
... ...

List subdomain

One might be interested in the known subdomains of a 2nd level domain name. ENTRADA by default stores the 2nd level domain name and the fully qualified domain name in two separate fields.

select qname, count(1) as tot
from dns.queries
where year=2015 and month=12
and domainname = "example.nl"
group by qname
order by tot desc

As result, we receive a list with the observed sub domains ordered by the number of queries:

qname tot
example.nl. 2500
imap.example.nl. 1230
ns1.example.nl. 980
... ...

Filter for DMARC queries

DMARC is a mechanism to define policies for email validation, dispositioning and reporting. These policies are defined in a DNS TXT record of a subdomain with the label "_dmarc". The query below filters for each query that asks for a domain name that has the string "_dmarc" as lowest label and asks for a TXT record (qtype 16). The results are ordered by the total number of queries for a qname.

select qname, count(1) as tot
from dns.staging
where qname LIKE '_dmarc.%'
and qtype = 16
group by qname
order by tot desc

Output:

qname tot
_dmarc.example.nl. 531
_dmarc.abc.nl. 472
_dmarc.tst.nl 288
... ...

Query origin

With the help of the Maxmind GeoIP database, ENTRADA automatically augments every query with the location of the querying resolver and adds this information to the country field. The following statement counts the observed countries for one domain name in December 2015.

select country, count(1) as tot
from dns.queries
where year=2015 and month=12
and domainname = "example.nl"
group by country
order by tot desc

Output:

country tot
NL 1712
US 623
DE 457
... ...

Relationship between domain names

It might be interesting to know, which domain names are related. In the example below, we list those domain names that have been queried by resolvers which have also queried the domain name example.nl on the same day.

select domainname, count(1) as tot
from dns.queries
where year=2015 and month=12 and day=1
and src in (
   select src
   from dns.queries
   where year=2015 and month=12 and day=1
   and domainname = "example.nl"
   group by src
)
group by domainname
order by tot desc

Output:

domainname tot
xyz.nl 2340
test.nl 3461
sidnlabs.nl 1224
... ...

Count query types

This statement helps to understand what kind of query types ENTRADA observes.

select qtype, count(1) as tot
from dns.queries
where year=2015 and month=12
group by qtype
order by tot desc

As a result, we get the total number of queries corresponding to a certain query type. See a list of query types here.

qtype tot
1 4 412 842 309
28 1 373 782 103
2 542 900 245
... ...

It is also possible to filter by specific query types. The statement below only shows queries with "unusual" query types, excluding queries for A, AAAA, MX or CNAME records. In this statement, only queries that are currently in the staging table are shown.

select qtype, count(1) as tot from dns.staging
where qtype != 1 and qtype != 2 and qtype != 5 and qtype != 6
and qtype !=15 and qtype != 16 and qtype != 28
and qtype != 43 and qtype != 48 and qtype != 255
and qtype != 99 and qtype != 12 and qtype != 33
and qtype != 35 and qtype != 38 and qtype != 46
group by qtype
order by counter desc
limit 1000

Output:

qtype tot
252 1 412
52 423
44 155
... ...

Most common NXDOMAINS

Get the domain names for which the server reponds most often with RCODE 3 (NXDOMAIN).

select qname,count(1) as tot
from dns.queries
where year=2016 and month=1 and day=11 and rcode=3
group by qname
order by tot desc
limit 1000

Output:

qname tot
xmpl.nl 323 693
exampel.nl 298 169
asdfjkl.nl 98 345
... ...

Get the ASs with the most resolvers

Count for each Autonomous System (AS) the number of resolvers. The AS-number is meta data which is added by ENTRADA while processing the pcap files.

select asn, count(distinct src) as tot
from dns.queries
where year=2016 and month=1
group by asn
order by tot desc
limit 1000

Output:

asn tot
AS64496 1 345
AS64510 985
AS64499 563
... ...

Get the busiest resolvers

Get the 1.000 resolvers from which we see most queries in one month.

select src, count(1) as tot
from dns.queries
where year=2016 and month=1
group by src
order by tot desc
limit 1000

Output:

asn tot
198.51.100.4 5 345 351 976
192.0.2.56 4 962 100 571
203.0.113.88 2 491 002 251
... ...

Get the resolvers with most unresolvable queries

Count for each resolver the number of queries for which the name server responded with RCODE 3 (NXDOMAIN).

select src, count(1) as tot
from dns.queries
where year=2016 and month=1 and rcode=3
group by src
order by tot desc
limit 1000

Output:

asn tot
198.51.100.4 863 315
192.0.2.56 500 571
203.0.113.88 332 251
... ...

Count queries coming from Google resolvers per day

This query counts for every day of the month January 2016 the number of queries the name servers received from open resolvers of Google. ENTRADA adds information, whether a query comes from open resolvers of Google or OpenDNS during the processing of the pcap data.

select day,count(1) as tot
from dns.queries
where year=2016 and month=1 and is_google
group by day
order by tot desc
limit 1000

Output:

day tot
1 4 234 912
2 2 561 370
3 3 126 614
... ...

Count how often long domain names are queried

If we are interested in especially long domains, then we can group domain names by length. The output is sorted by length of domain name.

select length(qname),count(1) as tot
from dns.queries
where year=2016 and month=1 and day=10
group by length(qname)
order by length(qname) desc
limit 1000

Output:

length(qname) tot
254 2
253 5
252 7
... ...

Measure the adoption of IPv6

The query below counts the number of DNS queries that the name server has received from resolvers with an IPv4 and an IPv6 address.

select ipv,count(1) as tot
from dns.queries
where year=2016 and month=1
group by ipv

Output:

ipv tot
4 267 270 863
6 58 453 399

Get queries with a certain qname

With substring matching it is possible to show every DNS query where the qname contains a certain string. with this functionality, we can detect possible phishing domains.

select qname, length(qname) as len
from dns.staging 
where qname LIKE "%paypal%"
group by qname
order by lengte desc

Output:

qname len
paypal.co.uk.dsadasfaf32.example.nl 35
www1.paypal.nl.uj2ds432.example.nl 33
paypal.nl.ujs2ds22.example.nl 29
... ...

Also, obvious misconfiguration can be revealed. The statement below shows how often domain names are queried that contain an "@" sign.

select qname, length(qname) as len, count(qname) as tot 
from dns.staging
where qname LIKE "%@%"
group by qname
order by counter desc,lengte desc

Output:

qname len tot
smtp@example.nl 15 180
mail@example.nl 15 172
alice@eexample.nl 16 145
... ... ...

Identify commonly used IP TTLs

It is also possible to draw conclusions from the commonly used IP packet Time To Live (TTL) of the querying resolvers. The TTL can be used to help determine the operating system a resolver is using, based on known initial TTL values for different operating systems. This statement creates slices of the length 32 and counts how many queries arrived with each TTL.

select ((ttl + 32) div 32) * 32 as slice, count(1) as tot
from dns.staging
group by slice
order by slice

Output:

slice tot
32 57 005
64 139 824 831
96 1 215 547
... ...

If we want to see, where queries with a default TTL of 64 are located, then the following statement is useful.

select ttl, country, count(1) as tot
from dns.staging
where ((ttl + 32) div 32) * 32 = 64
group by ttl,country
order by total desc

Output:

ttl country tot
61 NL 22 339 238
60 DE 12 081 219
59 NL 11 298 978
... ... ...

Show how many DNS responses are not received by the resolver

The ICMP destination unreachable message can be returned by a router or firewall in the path between the name server and the requesting resolver. Indicating that a resolver address is not reachable, this can be an indication of spoofed UDP DNS packets. This query focuses on the ICMP capabilities of ENTRADA. It counts every ICMPv4 packet with type 3 (destination unreachable) and groups it by source IP address.

select ip_src, count(ip_src) as tot
from icmp.staging
where icmp_type = 3 and ip_v = 4
group by ip_src
order by counter desc

Output:

ip_src tot
198.51.100.4 10 245
192.0.2.56 5 641
203.0.113.88 4 912
... ...

In case we are interested why resolvers were not reachable, we can group the packets by icmp code.

select icmp_type, icmp_code, count(1) as tot
from icmp.staging
where icmp_type=3
group by icmp_type, icmp_code
order by total desc

Output:

icmp_type icmp_code tot
3 3 393 199
3 1 3 820
3 0 2 005
... ... ...