homepage
Menu
Open menu
  • Training
    Go one level top Back

    Training

    • Courses

      Build cyber prowess with training from renowned experts

    • Hands-On Simulations

      Hands-on learning exercises keep you at the top of your cyber game

    • Certifications

      Demonstrate cybersecurity expertise with GIAC certifications

    • Ways to Train

      Multiple training options to best fit your schedule and preferred learning style

    • Training Events & Summits

      Expert-led training at locations around the world

    • Free Training Events

      Upcoming workshops, webinars and local events

    • Security Awareness

      Harden enterprise security with end-user and role-based training

    Featured: Solutions for Emerging Risks

    Discover tailored resources that translate emerging threats into actionable strategies

    Risk-Based Solutions

    Can't find what you are looking for?

    Let us help.
    Contact us
  • Learning Paths
    Go one level top Back

    Learning Paths

    • By Focus Area

      Chart your path to job-specific training courses

    • By NICE Framework

      Navigate cybersecurity training through NICE framework roles

    • DoDD 8140 Work Roles

      US DoD 8140 Directive Frameworks

    • By European Skills Framework

      Align your enterprise cyber skills with ECSF profiles

    • By Skills Roadmap

      Find the right training path based on critical skills

    • New to Cyber

      Give your cybersecurity career the right foundation for success

    • Leadership

      Training designed to help security leaders reduce organizational risk

    • Degree and Certificate Programs

      Gain the skills, certifications, and confidence to launch or advance your cybersecurity career.

    Featured

    New to Cyber resources

    Start your career
  • Community Resources
    Go one level top Back

    Community Resources

    Watch & Listen

    • Webinars
    • Live Streams
    • Podcasts

    Read

    • Blog
    • Newsletters
    • White Papers
    • Internet Storm Center

    Download

    • Open Source Tools
    • Posters & Cheat Sheets
    • Policy Templates
    • Summit Presentations
    • SANS Community Benefits

      Connect, learn, and share with other cybersecurity professionals

    • CISO Network

      Engage, challenge, and network with fellow CISOs in this exclusive community of security leaders

  • For Organizations
    Go one level top Back

    For Organizations

    Team Development

    • Why Partner with SANS
    • Group Purchasing
    • Skills & Talent Assessments
    • Private & Custom Training

    Leadership Development

    • Leadership Courses & Accreditation
    • Executive Cybersecurity Exercises
    • CISO Network

    Security Awareness

    • End-User Training
    • Phishing Simulation
    • Specialized Role-Based Training
    • Risk Assessments
    • Public Sector Partnerships

      Explore industry-specific programming and customized training solutions

    • Sponsorship Opportunities

      Sponsor a SANS event or research paper

    Interested in developing a training plan to fit your organization’s needs?

    We're here to help.
    Contact us
  • Talk with an expert
  • Log In
  • Join - it's free
  • Account
    • Account Dashboard
    • Log Out
  1. Home >
  2. Blog >
  3. DNS Anomaly Analysis Tips: Did you put a new cover sheet on that DDD report?
Chris Crowley
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
Cote D'ivoire
Croatia (Local Name: Hrvatska)
Curacao
Cyprus
Czech Republic
Democratic Republic of the Congo
Djibouti
Dominica
Dominican Republic
East Timor
Ecuador
Egypt
El Salvador
Equatorial Guinea
Eritrea
Estonia
Eswatini
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
Kiribati
Korea, Republic Of
Kosovo
Kuwait
Kyrgyzstan
Lao People's Democratic Republic
Latvia
Lebanon
Lesotho
Liberia
Liechtenstein
Lithuania
Luxembourg
Macau
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
North Macedonia
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
Saudi Arabia
Senegal
Serbia
Seychelles
Sierra Leone
Sint Maarten
Slovakia
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
Sweden
Switzerland
Taiwan
Tajikistan
Tanzania, United Republic Of
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 State
Venezuela
Vietnam
Virgin Islands (British)
Virgin Islands (U.S.)
Wallis And Futuna Islands
Western Sahara
Yemen
Zambia
Zimbabwe

By providing this information, you agree to the processing of your personal data by SANS as described in our Privacy Policy.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Tags:
  • Offensive Operations, Pen Testing, and Red Teaming

Related Content

Blog
HackFest_blog_image.png
Offensive Operations, Pen Testing, and Red Teaming, Open-Source Intelligence (OSINT), Penetration Testing and Red Teaming
November 16, 2023
A Visual Summary of SANS HackFest Summit
Check out these graphic recordings created in real-time throughout the event for SANS HackFest Summit 2023
No Headshot Available
Alison Kim
read more
Blog
Offensive Operations, Pen Testing, and Red Teaming, Penetration Testing and Red Teaming
October 4, 2023
SEC670 Prep Quiz Answers
Answers for the SEC670 Prep Quiz. For more details about the course and the quiz, please clickthrough to see the quiz article.
370x370_jonathan-reiter.jpg
Jonathan Reiter
read more
Blog
Offensive Operations, Pen Testing, and Red Teaming, Penetration Testing and Red Teaming
October 4, 2023
Take The Prerequisite SEC670 Quiz
This ten question quiz will help you in determining if you are ready to take SEC670.
370x370_jonathan-reiter.jpg
Jonathan Reiter
read more
  • Company
  • Mission
  • Instructors
  • About
  • FAQ
  • Press
  • Contact Us
  • Careers
  • Policies
  • Training Programs
  • Work Study
  • Academies & Scholarships
  • Public Sector Partnerships
  • Law Enforcement
  • SkillsFuture Singapore
  • Degree Programs
  • Get Involved
  • Join the Community
  • Become an Instructor
  • Become a Sponsor
  • Speak at a Summit
  • Join the CISO Network
  • Award Programs
  • Partner Portal
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
Cote D'ivoire
Croatia (Local Name: Hrvatska)
Curacao
Cyprus
Czech Republic
Democratic Republic of the Congo
Djibouti
Dominica
Dominican Republic
East Timor
Ecuador
Egypt
El Salvador
Equatorial Guinea
Eritrea
Estonia
Eswatini
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
Kiribati
Korea, Republic Of
Kosovo
Kuwait
Kyrgyzstan
Lao People's Democratic Republic
Latvia
Lebanon
Lesotho
Liberia
Liechtenstein
Lithuania
Luxembourg
Macau
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
North Macedonia
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
Saudi Arabia
Senegal
Serbia
Seychelles
Sierra Leone
Sint Maarten
Slovakia
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
Sweden
Switzerland
Taiwan
Tajikistan
Tanzania, United Republic Of
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 State
Venezuela
Vietnam
Virgin Islands (British)
Virgin Islands (U.S.)
Wallis And Futuna Islands
Western Sahara
Yemen
Zambia
Zimbabwe

By providing this information, you agree to the processing of your personal data by SANS as described in our Privacy Policy.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
  • Privacy Policy
  • Terms and Conditions
  • Do Not Sell/Share My Personal Information
  • Contact
  • Careers
© 2025 The Escal Institute of Advanced Technologies, Inc. d/b/a SANS Institute. Our Terms and Conditions detail our trademark and copyright rights. Any unauthorized use is expressly prohibited.
  • Twitter
  • Facebook
  • Youtube
  • LinkedIn