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.
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:
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:
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
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
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
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.
|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
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
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
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
|198.51.100.4||5 345 351 976|
|192.0.2.56||4 962 100 571|
|203.0.113.88||2 491 002 251|
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
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
|1||4 234 912|
|2||2 561 370|
|3||3 126 614|
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
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
|4||267 270 863|
|6||58 453 399|
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
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
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
|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
|61||NL||22 339 238|
|60||DE||12 081 219|
|59||NL||11 298 978|
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
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