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 attributes. 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 entrada.dns
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:

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 entrada.dns
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:

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:

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 entrada.dns
where year=2015 and month=12
and domainname = "example.nl"
group by country
order by tot desc

Output:

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 entrada.dns
where year=2015 and month=12 and day=1
and src in (
   select src
   from entrada.dns
   where year=2015 and month=12 and day=1
   and domainname = "example.nl"
   group by src
)
group by domainname
order by tot desc

Output:

Count query types

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

select qtype, count(1) as tot
from entrada.dns
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.

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:

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 entrada.dns
where year=2016 and month=1 and day=11 and rcode=3
group by qname
order by tot desc
limit 1000

Output:

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 entrada.dns
where year=2016 and month=1
group by asn
order by tot desc
limit 1000

Output:

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 entrada.dns
where year=2016 and month=1
group by src
order by tot desc
limit 1000

Output:

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 entrada.dns
where year=2016 and month=1 and rcode=3
group by src
order by tot desc
limit 1000

Output:

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 entrada.dns
where year=2016 and month=1 and is_google
group by day
order by tot desc
limit 1000

Output:

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 entrada.dns
where year=2016 and month=1 and day=10
group by length(qname)
order by length(qname) desc
limit 1000

Output:

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 entrada.dns
where year=2016 and month=1
group by ipv

Output:

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:

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:

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:

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:

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:

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: