DNS Anomaly Analysis Tips: Did you put a new cover sheet on that DDD report?

By Chris Crowley

“What is a DDD report?” you’re wondering.  That’s my pet name for a Daily DNS Delta.

You see, human beings are creatures of habit. Some have excellent habits, some have gross habits, some actually wear habits, but whatever works for you, we all are creatures of habit. We can use this feature of humanity to identify behavior to investigate within our network.

Short story is that most people go to the exact same websites every day. Every single day of their lives, they go to the exact same sites…so a request to a new site is essentially an anomaly, worthy of investigation. If a user goes to a really weird website in some strange location, as a security person, I’d like to know so I can follow up.  I really like daily reports that are actionable.

So, how do you know what sites users are going to? DNS query logs are a fantastic source of this information. I’ve created a         script to         help you generate DDDs for your network.  The script is at the end of this blog post… simply copy and paste it into your machine, mark it as readable and executable, provide it a data source, and it’s ready to roll.  Below, I’ll show  you how to configure your DNS servers to generate data for it to analyze, and then walk you through some of my analytic steps.

To use this script, you’ll need a source of data from your DNS server.  For that, you’ll need to enable query logging if you don’t do it already. I’m going to use BIND as an example here, but I could probably be convinced to adapt this script to MS DNS server. Or, someone out there can take that as an exercise left to the reader and enhance this script to pull logs from your MS DNS servers

The first step is to be sure you’re logging DNS queries. In your BIND config you would set something like:

       channel "querylog" {
               file "/var/log/bind/query.log";
               print-time yes;
      category queries { querylog; };

Additionally, some other BIND reference info can be found  here.

You’ll also need to make sure you have DBD for SQLite installed in your Perl environment. We’re going to use SQLite to store our records over time:

$ sudo perl -MCPAN "install DBD::SQLite"
## I know, I know Mark & Josh
## One day I won't be in a hurry and I'll learn Python and can rattle off scripts in Python like the rest of the cool kids. For now, it is my creaking bones Perl foo

You’ll also need Whois parser for perl:

$ sudo perl -MCAP "install Net::Whois::Parser"

because the script also includes the creation date of the domain gathered from whois.

Are there domains out there that are terrible and old? Sure. Are there domains that are brand new and totally legit? Yes. But a domain that is relatively new, and being accessed for the first time in your network, is definitely worthy of investigation. You could extend this script to include whatever relevant information you are interested in. You could even include a link to a Spam reputation site, for example.

Ah, so we have a bit of a problem here. You apparently didn’t read the script before running it so you didn’t change the necessary lines.

my $send_from = 'didyouseethememoaboutthis@montance.com';
my $send_to = 'illmakesureyougetanothercopyofthatmemo@blackhillsinfosec.com';
### Set this to the correct directory
my $database = "/var/log/bind/tps_dns.db";

I’ll get you a copy of that memo.

There are a couple of things that will happen if you run this in your environment. First, you’ll learn about a lot of new sites that people are going to on the internet. Seriously, that’s good for you…most of the time.

Second, you’ll see domains that use DNS queries for interesting purposes. For example, check out here how Sophos uses the sophosxl.net domain to validate web sites… http://community.sophos.com/t5/Sophos-EndUser-Protection/Excessive-DNS-lookups-from-sophosxl-net/td-p/11957 ). How do I know about this? Well, I was looking at the logs:


…and I noticed that the sophosxl.net was consistently including lots of new domains every day. I plan to add a capability to suppress items from the report based on an array specified in the script itself.

Another thing I noticed in review of a couple of weeks worth of data was the presence of unqualified host lookups. This is kind of weird, and I’ve sent it to the network manager indicating that this is something that definitely needs to be investigated. This data set is for a network that I don’t manage so there is some blindness to my knowledge of the data that I’m parsing and reviewing but that isn’t uncommon when dealing with DNS data because you honestly have no idea what people are browsing.


After a bit of research and testing, I believe these unqualified DNS requests are associated with Google Chrome startup, cache priming, and Chrome’s attempt to identify malicious DNS responses as you can see in this Chrome discussion post. In my next blog post, I plan to explore the techniques for correlating processes with the DNS queries that those processes make.

The script is yours to reuse, manipulate, and update. If you do add some cool enhancement, or fix my terribly hackish Perl code, please email me and let me know. If you find a problem in your network with this, I would be very interested in hearing about that as well. You don’t need to give me specifics, but I look forward to your TPS report. You will need to use the new coversheet.

Thank you very much to Johannes Ullrich for the DNS query logs found here that I used in testing this script.


  • Customize queries for creation dates for country codes, since they can (and do) use alternate fields than creation date
  • Add an array and code that eliminates some domains from the report – perhaps auto populated with in-addr.arpa and ip6.arpa
  • Care about record types (A, AAAA, etc)
  • MS DNS query log parsing
  • Figure out DBI selectall_hashref
  • Learn python in SANS SEC573

-Chris Crowley
SANS Instructor

P.S. In addition to the mobile stuff I do, I have a lot of tips and tricks for Incident Response. You can catch more of them when I teach SEC504 Hacker Techniques, Exploits & Incident Handling or check out the course that I author: MGT535  Incident Response Team Management.

The DDD Script


## @CCrowMontance 2015
## all wrongs reversed
## not necessarily fit for any purpose whatsoever, use at your own risk

#It's like this
scalar(@ARGV) == 1 or die "Usage $0 dns_log_file\n";

use Time::ParseDate;
use DBI;
##use strict;
##use warnings;
use Net::Whois::Parser;
$Net::Whois::Parser::GET_ALL_VALUES = 1;

my $send_from = 'didyouseethememoaboutthis@montance.com';
# ### CHANGE THIS EMAIL ADDRESS!!!!!!!!!!!!!
my $send_to = 'illgoaheadandmakesureyougetanothercopyofthatmemo@blackhillsinfosec.com';
# If quiet=1, don't print results to screen when script runs.
my $quiet=0;
my $nowhois=0; ## Default is 0
##my $email=1;
my $email=1; ## Default is 1

my @d_ary = ();
my $d="";
my $mailfile = '/tmp/DDD_report.eml';
my $line = "";
my $record = "";
my $driver = "SQLite";
my $database = "/var/log/bind/tps_dns.db";
my $tablename= "DDD";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened $database successfully\n";
## Check DNS TPS table exists format

my %new_q = {};
my %created = {};
# $new_q{"www.google.com"}=1;

# from http://www.perlmonks.org/?node_id=284436
## But, beware, there is a typo on the code on that page. ;-) cc

sub table_exists {
    my $db = shift;
    my $table = shift;
    my @tables = $db->tables('','','','TABLE');
    if (@tables) {
        for (@tables) {
              next unless $_;
              return 1 if $_ eq $table
   else {
       eval {
          local $db->{PrintError} = 0;
          local $db->{RaiseError} = 1;
          $db->do(qq{SELECT * FROM $table WHERE 1 = 0 });
     return 1 unless $@;
   return 0;

if (table_exists( $dbh, "$tablename")) {
    print "$tablename is there!\n";
else {
    print "$tablename table not found!\n Creating it now.\n";
       my $create_query = qq(CREATE TABLE $tablename
              LASTSEEN DATE NOT NULL););

        $dbh->do( $create_query );


my @abbr = qw( Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec );

my $key_field="RECORD";
my $all= "SELECT * FROM $tablename";
my $rec_ary = $dbh->selectall_arrayref($all); ## Yes, I know I should be able to do this with selectall_hashref. But it wasn't working for me.

# so I did this:
foreach my $row (@$rec_ary) {
     my ($id, $first, $last) = @$row;
     $rec_ref{$id} = $first;

while (<>) {
   $line = $_;
my @temp=split(/ /,$_);
 #### Expected space delimited fields:
 # date
 # time
 # "queries:" seems to be logging tag
 # "info:" log level
 # "client"
 # ip#port
 # "query:" - query type
 # query made
 # "IN" -
 # record type
 # (DNS Server)

my $count=0;
if ( $temp[6] eq "query:" ) {


     if ( $rec_ref{$record} ne "" ) {
            if ( $new_q{$record} ne "" ) {
     } else {
              my $insert_query = qq(INSERT INTO $tablename VALUES ("$record","$temp[0]","$temp[0]") );
              $dbh->do( $insert_query );
              ## update indexed array
              $rec_ref{$record} = $temp[0];
              ## push onto new array for this report, and initialize

              my $d_tmp=pop(@d_ary);
              if ( length($d_tmp) == 2 ) {
               } else {

               if ( $nowhois == 0) {
                   my $whois = parse_whois(domain => $d);
                   if ( $whois->{creation_date}[0] eq "") {
                   $whois->{creation_date}[0] = "creation date unknown";
                } else {

       #my $ary = $dbh->selectrow_arrayref("SELECT * FROM $tablename WHERE RECORD = $record");
       #print join(" ", @$ary), "\n";

  } ## end if query

} ## end while (<>)

## Report

#foreach $key (sort(keys %$rec_ref)) {
# print $key, '=', $rec_ref{$key}, "\n";
my $newcount = scalar(keys(%new_q));
if ( $newcount > 1 ) {
      die "failed to open $mailfile" unless (open (INFILE, '>', "$mailfile"));
      print INFILE "From: TPS <$send_from>\n";
      print INFILE "Subject: DDD Report\n\n";
      ## header must have blank line after it
      if ($quiet == 0) {
               print "\n\n\n\n\nNew Records in this batch:\n";
      print INFILE "\n\nNew Records in this batch:\n";
      foreach $key (sort(keys %new_q)) {
          if ($quiet == 0) {
               print "$key count: $new_q{$key}\t\tdomain created: $created{$key}\n";
      print INFILE "$key count: $new_q{$key}\t\tdomain created: $created{$key}\n";
   close (INFILE);
   # Ya, not the right cover sheet at all
   if ($email == 1) {
   system ("cat $mailfile | /usr/lib/sendmail $send_to");


Modifying Android Apps: A SEC575 Hands-on Exercise, Part 2

By Joshua Wright



In the last installment of this article, we looked at the IsItDown application, and how it is designed not to run in the Android Emulator, and to include a super-annoying banner ad. We showed how the Apktool utility can be used to decompile an Android APK file, and how we can evaluate and modify the produced Smali code to manipulate the application’s functionality.

In this final installment, we’ll re-build the IsItDown application with our Smali file changes, then we’ll generate the necessary keys and sign the application so that it can be used in the modified form on a virtual or a physical Android device. We’ll also look at techniques that can be applied to defend against this hack, with advice that you can pass on to Android developers.

Rebuild the App

With the completed changes to the Smali files saved, we can re-build the app. We return to the IsItDown directory created by Apktool when we decompiled the app, and rebuild:


As long as there are no syntax errors in your modified Smali code, Apktool will build a new Android APK file in the dist directory:


Sign the App

We can’t install and run this new version yet though, because the Android platform requires signed application packages. Never fear though – the signature isn’t anything that is checked (by anyone, ever), and can be self-signed.

First, use the JDK tools to run the keytool utility and create your own signing key as shown here:


Note that the path to the keytool.exe utility might be different for you depending on your version of the JDK. You can feel free to enter any values you find entertaining here, just make the alias “IsItDown” as shown. Next, use the jarsigner.exe utility to leverage the key and generate a signed application package:


With the jarsigner utility, specify the location of your keystore file, the APK filename, and the keystore alias “IsItDown”. Enter the password you supplied when you generated the keystore and ignore the warning. Viola! Signed Android package!

Run the App

Finally, we get to reap the benefits of our work. Uninstall the old version of IsItDown, and install the modified version.


Next up, run the app and see your changes in action. AWESOME!


Defend the App

Now that we’ve seen the steps to manipulate the app, we should take a look at what developers can do about this. As a pen tester, it’s important to always leave your customer with more than “this is broken”: we need to address the audience and give them advice on what to do about the issues you’ve identified.

Here… it’s hard. The bottom line is that anyone can modify and manipulate an Android app or any software (on any device not using end-to-end trusted execution policies). The developer can do things that make it harder for an attacker, but ultimately there is no reasonable way to stop a determined attacker from manipulating the application.

One easy opportunity for Android developers is to check the integrity of the application signature. When we generate a new APK file with Apktool, we have to also generate a self-signed key and sign the APK file before we can install it. Since we don’t have the original developer’s signing key, we can never reproduce the original signature information.

A developer can periodically check the certificate information for the application at runtime. I saw this technique first in Android Security Cookbook by Keith Makan and Scott Alexander-Bown, Scott (2013, Packt Publishing, page 179). Their code doesn’t work on newer Android API levels, but I’ve modified it with a complete example available at https://github.com/joswr1ght/ValidateSigningCertificate.

Essentially, the developer generates the signing certificate, and calculates a SHA1 hash of the certificate, embedding it in the application source:


Periodically during the application execution, the developer calls getPackageInfo() with the PackageManager.GET_SIGNATURES argument to retrieve the app signing certificate information, comparing the embedded SHA1 certificate hash with the hash of the current certificate:


This is… OK. An attacker who can modify the Smali code can also modify the embedded SHA1 of the developer’s certificate to match their own signing certificate, or just disable the certificate checks altogether. The only “superior” defense is to move sensitive or critical code into a natively-compiled library with the Android Native Development Kit (NDK). Here, the developer would re-write their Java code in C/C++ to make it harder to manipulate the app’s functionality (harder, but still not impossible, since many attackers can manipulate native libraries too).


In this article we’ve looked at the techniques for manipulating Android apps. Our IsItDown target app was a straightforward target, but you can use the same techniques on many other Android apps with just a little added patience.

Remember to use these techniques for good, and not evil. That game you like to play was written by someone who needs to make money too, and turning off their banner ads or manipulating the “coin” value x100 is fun for you, but it makes them think twice about implementing their next project too. Use these techniques ethically.

As a parting note: my friends who write iOS apps sputter about how iOS apps are natively compiled and can’t be reverse-engineered this same way. Whenever I do iOS reverse-engineering, I take a look for the Android version of the same app. Since Android reverse-engineering is so much easier, we can spend a little time looking at the Android code before moving onto the iOS app to get a feel for how the app functions. If I’m attacking the back-end servers, I don’t have to reverse-engineer the iOS version of the app; the Android version will do just fine.




Upcoming SANS Special Event – 2018 Holiday Hack Challenge


SANS Holiday Hack Challenge – KringleCon 2018

  • Free SANS Online Capture-the-Flag Challenge
  • Our annual gift to the entire Information Security Industry
  • Designed for novice to advanced InfoSec professionals
  • Fun for the whole family!!
  • Build and hone your skills in a fun and festive roleplaying like video game, by the makers of SANS NetWars
  • Learn more: www.kringlecon.com
  • Play previous versions from free 24/7/365: www.holidayhackchallenge.com

Player Feedback!

  • “On to level 4 of the #holidayhackchallenge. Thanks again @edskoudis / @SANSPenTest team.” – @mikehodges
  • “#SANSHolidayHack Confession – I have never used python or scapy before. I got started with both today because of this game! Yay!” – @tww2b
  • “Happiness is watching my 12 yo meet @edskoudis at the end of #SANSHolidayHack quest. Now the gnomes #ProudHackerPapa” – @dnlongen