Fortunately, the good folks over at Alienvault also maintain an IP reputation database that you can download for free. After learning of this, my first thought was "How can I make use of this?". Then I thought, "Wouldn't it be cool to store their IP Reputation list in my own database?". And that's just what I did.
I wrote the following script and set it up to run as a cron job every 2 hours. They update the list you can download every 1 hour.
#!/bin/sh
wget https://reputation.alienvault.com/reputation.snort -P /tmp/ --no-check-certificate -N
sed -n '/^[0-9]/p' /tmp/reputation.snort > /tmp/iprep.out
/path/to/loadiprep.sh
The above script will use wget to download the latest reputation.snort file from Alienvault. You don't have to use this list with Snort. It just happens to have exactly the information I need for my database in it, so that's the one I went with from their website.
It gets downloaded to your local /tmp directory and then I pull out the IP addresses and reputation information and place it in /tmp/iprep.out.
Finally, it runs loadiprep.sh. That script looks like this:
#!/usr/bin/perl
use CGI qw(:standard);
use DBI;
$opt_user='<user>';
$opt_password='<password>';
$mydb='reputation';
$host='localhost';
$dbh = DBI->connect("DBI:mysql:$mydb:$host",$opt_user,$opt_password) or
die("ERROR");
{
my $query1 = "load data infile '/tmp/iprep.out' replace into table iprep fields terminated by ' # ' lines terminated by '\n' (ip, reputation)";
my $statement = $dbh->prepare($query1);
$statement->execute();
$statement->finish;
}
$rc=$dbh->disconnect;
On my machine I created a MySQL database called "reputation". In that database I created a table called "iprep". The iprep table contains two columns called "ip" and "reputation". The ip column contains the IP addresses and the reputation column contains, you guessed it, the reputation information.
The loadiprep.sh script that is called by the first script I mentioned will populate the database with the downloaded IP reputation information. It will stay updated if you are running it with a cron job like I am. Don't forget to change <user> and <password> in the above script to a user and pass that has access to your reputation database.
Right now my iprep table contains over 300,000 rows. In looking for a way to query this data without the need to log into MySQL each time, I came up with the following:
#!/usr/bin/perl -wIf you save the above perl script as "queryrepdb.pl", you would execute it from the command line like this: ./queryrepdb.pl somefile.txt, where somefile.txt contains a list of IP addresses that you want to check, each on its own line. The script will return no output if none of the IP addresses are found in the database. But if a match is found, it will print the IP address and reputation information to the console.
use DBI;
$dbh = DBI->connect('dbi:mysql:reputation','<user>','<password>')
or die "Connection Error: $DBI::errstr\n";
$LOGFILE = "$ARGV[0]";
open(LOGFILE) or die("Could not open log file.");
foreach $line (<LOGFILE>) {
chomp($line); # remove the newline from $line.
$sql = "select ip,reputation from iprep where ip = ?";
$sth = $dbh->prepare($sql);
$sth->execute($line)
or die "SQL Error: $DBI::errstr\n";
while (@row = $sth->fetchrow_array) {
print join(" ", @row), "\n";
}
}
I took it a step further with another script:
#!/usr/bin/perl -w
use DBI;
$PCAPFILE = "$ARGV[0]";
system qq(`tcpdump -tnr $PCAPFILE | awk -F '.' '{print \$1"."\$2"."\$3"."\$4}' | sort | uniq | sed 's/^...//g' > samp.txt` );
$dbh = DBI->connect('dbi:mysql:reputation','<user>',<password>')
or die "Connection Error: $DBI::errstr\n";
$LOGFILE = "samp.txt";
open(LOGFILE) or die("Could not open log file.");
foreach $line (<LOGFILE>) {
chomp($line);
$sql = "select ip,reputation from iprep where ip = ?";
$sth = $dbh->prepare($sql);
$sth->execute($line)
or die "SQL Error: $DBI::errstr\n";
while (@row = $sth->fetchrow_array) {
print join("\t\t", @row), "\n";
}
}
This script will also take a file as input. This time however, the file should be a pcap (packet capture) file, such as one created with tcpdump or wireshark.
The script will then create a list of the unique source and destination IP addresses from the pcap using tcpdump to read the file. The IP list is stored in a file called samp.txt. That file is then read and the addresses are compared to the data that resides in the reputation database. If no matches are found, the script will output nothing. Again, if a match is found, the IP address and reputation information are printed to the console.
That last script only matches against the source and destination IP addresses in the pcap. If there is a host domain listed in the packet, such as a visited URL, it is not looked up by this script. However, I would encourage you to run an nslookup against the domain and then run a search against the IP address you get against the reputation database.
If you just want to query it directly from mysql, the query is very simple:
use reputation;
select * from iprep where ip = 'xx.xx.xx.xx';
If you come across a pcap with some addresses you aren't sure are malicious or not, this would be a great first step in determining whether or not you should be concerned. This is not the end all, be all of determining whether or not it is safe, though, and you should also follow your normal network security procedures when trying to determine if a host poses a threat to your network.
