"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:
...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");
}
}
