homepage
Open menu
Go one level top
  • Train and Certify
    • Overview
    • Get Started in Cyber
    • Courses
    • GIAC Certifications
    • Training Roadmap
    • OnDemand
    • Live Training
    • Summits
    • Cyber Ranges
    • College Degrees & Certificates
    • Scholarship Academies
    • NICE Framework
    • Specials
  • Manage Your Team
    • Overview
    • Group Purchasing
    • Why Work with SANS
    • Build Your Team
    • Hire Cyber Talent
    • Team Development
    • Private Training
    • Security Awareness Training
    • Leadership Training
    • Industries
  • Resources
    • Overview
    • Internet Storm Center
    • White Papers
    • Webcasts
    • Tools
    • Newsletters
    • Blog
    • Podcasts
    • Posters & Cheat Sheets
    • Summit Presentations
    • Security Policy Project
  • Focus Areas
    • Cyber Defense
    • Cloud Security
    • Digital Forensics & Incident Response
    • Industrial Control Systems
    • Cyber Security Leadership
    • Offensive Operations
  • Get Involved
    • Overview
    • Join the Community
    • Work Study
    • Teach for SANS
    • CISO Network
    • Partnerships
    • Sponsorship Opportunities
  • About
    • About SANS
    • Our Founder
    • Instructors
    • Mission
    • Diversity
    • Awards
    • Contact
    • Frequently Asked Questions
    • Customer Reviews
    • Press
  • SANS Sites
    • GIAC Security Certifications
    • Internet Storm Center
    • SANS Technology Institute
    • Security Awareness Training
  • Search
  • Log In
  • Join
    • Account Dashboard
    • Log Out
  1. Home >
  2. Blog >
  3. DNS Anomaly Analysis Tips: Did you put a new cover sheet on that DDD report?
370x370_Christopher-Crowley.jpg
Christopher Crowley

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

July 10, 2015

"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:

logging{
       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.

### CHANGE THIS EMAIL ADDRESS!!!!!!!!!!!!!
my $send_from = 'didyouseethememoaboutthis@montance.com';
### CHANGE THIS EMAIL ADDRESS!!!!!!!!!!!!!
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:

sophosxl-newqueries.png

...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.

TO DO:

  • 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

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

#!/usr/bin/perl

## @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;

### CHANGE THIS EMAIL ADDRESS!!!!!!!!!!!!!
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 <a href="http://www.perlmonks.org/?node_id=284436" class="redactor-autoparser-object">http://www.perlmonks.org/?node...</a>
## 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
             (RECORD TEXT PRIMARY KEY NOT NULL,
              FIRSTSEEN DATE NOT NULL,
              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 (<>) {
   chomp;
   $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:" ) {

     $record="$temp[7]";

     if ( $rec_ref{$record} ne "" ) {
            if ( $new_q{$record} ne "" ) {
                     $new_q{$record}++;
             }
     } else {
            ##INSERT
              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
              $new_q{$record}=0;
              $new_q{$record}++;

              @d_ary=split(/\./,$record);
              my $d_tmp=pop(@d_ary);
              if ( length($d_tmp) == 2 ) {
                    $d=pop(@d_ary).".".$d_tmp;
                    $d=pop(@d_ary).".".$d;
               } else {
                    $d=pop(@d_ary).".".$d_tmp;
               }

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

       }
       #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");
   }
}
Share:
TwitterLinkedInFacebook
Copy url Url was copied to clipboard
Subscribe to SANS Newsletters
Receive curated news, vulnerabilities, & security awareness tips
United States
Canada
United Kingdom
Spain
Belgium
Denmark
Norway
Netherlands
Australia
India
Japan
Singapore
Afghanistan
Aland Islands
Albania
Algeria
American Samoa
Andorra
Angola
Anguilla
Antarctica
Antigua and Barbuda
Argentina
Armenia
Aruba
Austria
Azerbaijan
Bahamas
Bahrain
Bangladesh
Barbados
Belarus
Belize
Benin
Bermuda
Bhutan
Bolivia
Bonaire, Sint Eustatius, and Saba
Bosnia And Herzegovina
Botswana
Bouvet Island
Brazil
British Indian Ocean Territory
Brunei Darussalam
Bulgaria
Burkina Faso
Burundi
Cambodia
Cameroon
Cape Verde
Cayman Islands
Central African Republic
Chad
Chile
China
Christmas Island
Cocos (Keeling) Islands
Colombia
Comoros
Cook Islands
Costa Rica
Croatia (Local Name: Hrvatska)
Curacao
Cyprus
Czech Republic
Democratic Republic of the Congo
Djibouti
Dominica
Dominican Republic
East Timor
East Timor
Ecuador
Egypt
El Salvador
Equatorial Guinea
Eritrea
Estonia
Ethiopia
Falkland Islands (Malvinas)
Faroe Islands
Fiji
Finland
France
French Guiana
French Polynesia
French Southern Territories
Gabon
Gambia
Georgia
Germany
Ghana
Gibraltar
Greece
Greenland
Grenada
Guadeloupe
Guam
Guatemala
Guernsey
Guinea
Guinea-Bissau
Guyana
Haiti
Heard And McDonald Islands
Honduras
Hong Kong
Hungary
Iceland
Indonesia
Iraq
Ireland
Isle of Man
Israel
Italy
Jamaica
Jersey
Jordan
Kazakhstan
Kenya
Kingdom of Saudi Arabia
Kiribati
Korea, Republic Of
Kosovo
Kuwait
Kyrgyzstan
Lao People's Democratic Republic
Latvia
Lebanon
Lesotho
Liberia
Liechtenstein
Lithuania
Luxembourg
Macau
Macedonia
Madagascar
Malawi
Malaysia
Maldives
Mali
Malta
Marshall Islands
Martinique
Mauritania
Mauritius
Mayotte
Mexico
Micronesia, Federated States Of
Moldova, Republic Of
Monaco
Mongolia
Montenegro
Montserrat
Morocco
Mozambique
Myanmar
Namibia
Nauru
Nepal
Netherlands Antilles
New Caledonia
New Zealand
Nicaragua
Niger
Nigeria
Niue
Norfolk Island
Northern Mariana Islands
Oman
Pakistan
Palau
Palestine
Panama
Papua New Guinea
Paraguay
Peru
Philippines
Pitcairn
Poland
Portugal
Puerto Rico
Qatar
Reunion
Romania
Russian Federation
Rwanda
Saint Bartholemy
Saint Kitts And Nevis
Saint Lucia
Saint Martin
Saint Vincent And The Grenadines
Samoa
San Marino
Sao Tome And Principe
Senegal
Serbia
Seychelles
Sierra Leone
Sint Maarten
Slovakia (Slovak Republic)
Slovenia
Solomon Islands
South Africa
South Georgia and the South Sandwich Islands
South Sudan
Sri Lanka
St. Helena
St. Pierre And Miquelon
Suriname
Svalbard And Jan Mayen Islands
Swaziland
Sweden
Switzerland
Taiwan
Tajikistan
Tanzania
Thailand
Togo
Tokelau
Tonga
Trinidad And Tobago
Tunisia
Turkey
Turkmenistan
Turks And Caicos Islands
Tuvalu
Uganda
Ukraine
United Arab Emirates
United States Minor Outlying Islands
Uruguay
Uzbekistan
Vanuatu
Vatican City
Venezuela
Vietnam
Virgin Islands (British)
Virgin Islands (U.S.)
Wallis And Futuna Islands
Western Sahara
Yemen
Yugoslavia
Zambia
Zimbabwe

Tags:
  • Penetration Testing and Ethical Hacking

Related Content

Blog
Purple Team, Penetration Testing and Ethical Hacking
March 17, 2022
Shifting from Penetration Testing to Red Team and Purple Team
Penetration Testing to Red Team is mentality. Red Team is "the practice of looking at a problem or situation from the perspective of an adversary".
370x370_Jorge-Orchilles.jpg
Jorge Orchilles
read more
Blog
Untitled_design-43.png
Digital Forensics and Incident Response, Cybersecurity and IT Essentials, Industrial Control Systems Security, Purple Team, Open-Source Intelligence (OSINT), Penetration Testing and Ethical Hacking, Cyber Defense, Cloud Security, Security Management, Legal, and Audit
December 8, 2021
Good News: SANS Virtual Summits Will Remain FREE for the Community in 2022
They’re virtual. They’re global. They’re free.
Emily Blades
read more
Blog
Penetration Testing and Ethical Hacking, Cyber Defense
December 10, 2019
EQL Threat Hunting
The Event Query Language (EQL) is a standardized query language (similar to SQL) to evaluate Windows events. Written by Russ Wolf, EQL is an amazing tool to normalize Windows log events for consistent access and query. In practice, EQL is most effective when working with Windows Event Log and...
370x370_Joshua-Wright.jpg
Joshua Wright
read more
  • Register to Learn
  • Courses
  • Certifications
  • Degree Programs
  • Cyber Ranges
  • Job Tools
  • Security Policy Project
  • Posters & Cheat Sheets
  • White Papers
  • Focus Areas
  • Cyber Defense
  • Cloud Security
  • Cyber Security Leadership
  • Digital Forensics
  • Industrial Control Systems
  • Offensive Operations
Subscribe to SANS Newsletters
Receive curated news, vulnerabilities, & security awareness tips
United States
Canada
United Kingdom
Spain
Belgium
Denmark
Norway
Netherlands
Australia
India
Japan
Singapore
Afghanistan
Aland Islands
Albania
Algeria
American Samoa
Andorra
Angola
Anguilla
Antarctica
Antigua and Barbuda
Argentina
Armenia
Aruba
Austria
Azerbaijan
Bahamas
Bahrain
Bangladesh
Barbados
Belarus
Belize
Benin
Bermuda
Bhutan
Bolivia
Bonaire, Sint Eustatius, and Saba
Bosnia And Herzegovina
Botswana
Bouvet Island
Brazil
British Indian Ocean Territory
Brunei Darussalam
Bulgaria
Burkina Faso
Burundi
Cambodia
Cameroon
Cape Verde
Cayman Islands
Central African Republic
Chad
Chile
China
Christmas Island
Cocos (Keeling) Islands
Colombia
Comoros
Cook Islands
Costa Rica
Croatia (Local Name: Hrvatska)
Curacao
Cyprus
Czech Republic
Democratic Republic of the Congo
Djibouti
Dominica
Dominican Republic
East Timor
East Timor
Ecuador
Egypt
El Salvador
Equatorial Guinea
Eritrea
Estonia
Ethiopia
Falkland Islands (Malvinas)
Faroe Islands
Fiji
Finland
France
French Guiana
French Polynesia
French Southern Territories
Gabon
Gambia
Georgia
Germany
Ghana
Gibraltar
Greece
Greenland
Grenada
Guadeloupe
Guam
Guatemala
Guernsey
Guinea
Guinea-Bissau
Guyana
Haiti
Heard And McDonald Islands
Honduras
Hong Kong
Hungary
Iceland
Indonesia
Iraq
Ireland
Isle of Man
Israel
Italy
Jamaica
Jersey
Jordan
Kazakhstan
Kenya
Kingdom of Saudi Arabia
Kiribati
Korea, Republic Of
Kosovo
Kuwait
Kyrgyzstan
Lao People's Democratic Republic
Latvia
Lebanon
Lesotho
Liberia
Liechtenstein
Lithuania
Luxembourg
Macau
Macedonia
Madagascar
Malawi
Malaysia
Maldives
Mali
Malta
Marshall Islands
Martinique
Mauritania
Mauritius
Mayotte
Mexico
Micronesia, Federated States Of
Moldova, Republic Of
Monaco
Mongolia
Montenegro
Montserrat
Morocco
Mozambique
Myanmar
Namibia
Nauru
Nepal
Netherlands Antilles
New Caledonia
New Zealand
Nicaragua
Niger
Nigeria
Niue
Norfolk Island
Northern Mariana Islands
Oman
Pakistan
Palau
Palestine
Panama
Papua New Guinea
Paraguay
Peru
Philippines
Pitcairn
Poland
Portugal
Puerto Rico
Qatar
Reunion
Romania
Russian Federation
Rwanda
Saint Bartholemy
Saint Kitts And Nevis
Saint Lucia
Saint Martin
Saint Vincent And The Grenadines
Samoa
San Marino
Sao Tome And Principe
Senegal
Serbia
Seychelles
Sierra Leone
Sint Maarten
Slovakia (Slovak Republic)
Slovenia
Solomon Islands
South Africa
South Georgia and the South Sandwich Islands
South Sudan
Sri Lanka
St. Helena
St. Pierre And Miquelon
Suriname
Svalbard And Jan Mayen Islands
Swaziland
Sweden
Switzerland
Taiwan
Tajikistan
Tanzania
Thailand
Togo
Tokelau
Tonga
Trinidad And Tobago
Tunisia
Turkey
Turkmenistan
Turks And Caicos Islands
Tuvalu
Uganda
Ukraine
United Arab Emirates
United States Minor Outlying Islands
Uruguay
Uzbekistan
Vanuatu
Vatican City
Venezuela
Vietnam
Virgin Islands (British)
Virgin Islands (U.S.)
Wallis And Futuna Islands
Western Sahara
Yemen
Yugoslavia
Zambia
Zimbabwe
  • © 2022 SANS™ Institute
  • Privacy Policy
  • Contact
  • Careers
  • Twitter
  • Facebook
  • Youtube
  • LinkedIn