Home > Uncategorized > Writing DNS lookup stuff to a DB using argus-client’s radump() and python

Writing DNS lookup stuff to a DB using argus-client’s radump() and python

A few times on the mailing list, the question of how to archive DNS lookup stuff has come up.

I spent a few days writing a python script that takes the output of radump(), parses and writes it to a DB. radump() is an argus-client example that takes the binary argus user data and prints it using protocol printers.

Creating the DB structure:
You must import the DB structure into the DB.
I have also posted a gist of the db structure.

For example:

cd
curl https://gist.github.com/mbrownnycnyc/6083357/raw/069c5f6b782c5623dc0d671a076c53b301193a6a/argus_dnsdb.sql > argus_dnsdb.sql
mysql -uroot -p < argus_dnsdb.sql

Create a user:
Here is some quick sql syntax to create a restricted user which you should be able to import as previous (change newpassword):

use mysql;
GRANT SELECT, INSERT ON argus_dnsdb.* TO 'argusdns'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'argusdns'@'localhost';

The DB Writer:
You can then use the DB writer.
I have posted a gist of the db writer.

For example (change newpassword):

cd
curl https://gist.github.com/mbrownnycnyc/6158144/raw/068f20728b116b977c670aed9539273f91693276/radump_to_dns_db.py > radump_to_dns_db.py
sed s/\"passwordhere\"/\"newpassword\"/ -i radump_to_dns_db.py

Processing DNS data from argus flow binary data:
To import from a file and see the output of the command (where argus.file is your file):

grep -v ^# /root/.rarc | grep -v ^$ > ~/for_dnsdb.rarc && if grep ^RA_TIME_FORMAT ~/for_dnsdb.rarc > /dev/null ; then sed s/^RA_TIME_FORMAT/#RA_TIME_FORMAT/g -i ~/for_dnsdb.rarc && echo -e "RA_TIME_FORMAT=\"%Y-%m-%d %T.%f\"\nRA_PRINT_LABELS=-1\nRA_FIELD_DELIMITER='^'" >> ~/for_dnsdb.rarc ; fi
radump -F ~/for_dnsdb.rarc -r argus.file -s seq ltime saddr daddr suser:1024 duser:1024 - port domain | python radump_to_dns_db.py

To connect to an argus server and not see the output (where 127.0.0.1:561 is your server):

grep -v ^# /root/.rarc | grep -v ^$ > ~/for_dnsdb.rarc && if grep ^RA_TIME_FORMAT ~/for_dnsdb.rarc > /dev/null ; then sed s/^RA_TIME_FORMAT/#RA_TIME_FORMAT/g -i ~/for_dnsdb.rarc && echo -e "RA_TIME_FORMAT=\"%Y-%m-%d %T.%f\"\nRA_PRINT_LABELS=-1\nRA_FIELD_DELIMITER='^'" >> ~/for_dnsdb.rarc ; fi
nohup radump -F ~/for_dnsdb.rarc -S 127.0.0.1:561 -s seq ltime saddr daddr suser:1024 duser:1024 - port domain | python radump_to_dns_db.py > /dev/null &

Rotating the DB:
If you wish to rotate the log, you may want to create a MySQL EVENT.

I have confirmed that this is a safe procedure and that the EVENT will fail immediately if an INSERT fails, as to avoid the destructive action of DELETE.

This EVENT runs at 00:00:05 every day.
It takes any record who’s query time occurred before the current day’s midnight, and places it into a table that was created with the name of the previous date as ‘%Y%m%d’.

use argus_dnsdb;
DELIMITER |
CREATE EVENT `dnsdb_rotator`
ON SCHEDULE
EVERY 1 DAY
STARTS date_format(now(), '%Y-%m-%d 00:00:05')
ON COMPLETION NOT PRESERVE
ENABLE
DO BEGIN
set @target_table_name=CONCAT('`argus_dnsdb`.`',date_format(date_sub(now(),interval 1 day), '%Y%m%d'),'`');
set @create_table_stmt_str = CONCAT('CREATE TABLE ',@target_table_name,' like `argus_dnsdb`.`main`;');
PREPARE create_table_stmt FROM @create_table_stmt_str;
EXECUTE create_table_stmt;
DEALLOCATE PREPARE create_table_stmt;
set @a=unix_timestamp(date_format(now(), '%Y-%m-%d 00:00:00'));
set @insert_stmt_str = CONCAT('INSERT INTO ',@target_table_name,' SELECT * FROM `argus_dnsdb`.`main` WHERE qtime < ',@a,' ;');
PREPARE insert_stmt FROM @insert_stmt_str;
EXECUTE insert_stmt;
DEALLOCATE PREPARE insert_stmt;
DELETE FROM `argus_dnsdb`.`main` WHERE qtime < @a ;
END;
|
DELIMITER ;

Client is upcoming:
I will be writing a client and updating this post with the gist. The client will be able to take in the following:

`dnsdb_query.py` [-c [separator char]] -T [timespan WHERE clause injection] -atype [regex] -qtype [regex] -type [regex] -qhost [regex] -ahost [regex] -host [regex] -nsserver [regex] -nsclient [regex]

You can perform counts using `| wc -l` for instance.

Advertisements
  1. Rahimeh
    July 26, 2013 at 9:01 pm

    Many Thanks,

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: