I have recently added the ability to log the client IP of passivedns from gamelinux (how to install passivedns on debian can be found here):
The client IP is already logged in the .log files, but not stored inside the MySQL DB. Here is the .patch script, which will add the functionality to the perl script which imports the data into MySQL:
--- pdns2db.pl.bak 2014-03-03 15:44:14.000000000 +0100
+++ pdns2db.pl 2014-03-03 15:53:54.000000000 +0100
@@ -306,9 +306,9 @@
eval{
$sql = qq[
INSERT INTO $TABLE_NAME (
- QUERY,RR,MAPTYPE,ANSWER,TTL,COUNT,LAST_SEEN,FIRST_SEEN
+ QUERY,RR,MAPTYPE,ANSWER,TTL,COUNT,LAST_SEEN,FIRST_SEEN,clientip
) VALUES (
- '$query','$rr','$type','$answer','$ttl','$count',FROM_UNIXTIME($ts),FROM_UNIXTIME($tsl)
+ '$query','$rr','$type','$answer','$ttl','$count',FROM_UNIXTIME($ts),FROM_UNIXTIME($tsl),'$cip'
) ON DUPLICATE KEY UPDATE
TTL = if (TTL < $ttl, $ttl, TTL),
COUNT = COUNT + '$count',
@@ -351,6 +351,7 @@
COUNT BIGINT(20) UNSIGNED NOT NULL DEFAULT '1', \
FIRST_SEEN DATETIME NOT NULL, \
LAST_SEEN DATETIME NOT NULL, \
+ CLIENTIP varchar(255) DEFAULT '', \
PRIMARY KEY (ID), \
UNIQUE KEY MARQ (MAPTYPE,ANSWER,RR,QUERY), \
KEY query_idx (QUERY), \You can apply the patch using “patch pdns2db.pl < pdns2db.pl_clientip.patch”
After that you have to alter your current MySQL table (if you already used passivedns) so that you have a column for “clientip”. Here are the commands:
(Login to DB): mysql -ppdns -updns pdns
(Actual DB command): alter table pdns add CLIENTIP varchar(256) after last_seen;If you get a permission denied problem, you may have to login as root, using “mysql -uroot -p pdns“.
You should then have a table like this:
Database changed
mysql> describe pdns;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| QUERY | varchar(255) | NO | MUL | | |
| MAPTYPE | varchar(10) | NO | MUL | | |
| RR | varchar(10) | NO | | | |
| ANSWER | varchar(255) | NO | MUL | | |
| TTL | int(10) | NO | | 0 | |
| COUNT | bigint(20) unsigned | NO | | 1 | |
| FIRST_SEEN | datetime | NO | | NULL | |
| LAST_SEEN | datetime | NO | | NULL | |
| CLIENTIP | varchar(256) | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
10 rows in set (0.14 sec)And entries like this:
mysql> select * from pdns where query like "%foobar%";
+--------+-----------+---------+----+-------------+-------+-------+---------------------+---------------------+-----------+
| ID | QUERY | MAPTYPE | RR | ANSWER | TTL | COUNT | FIRST_SEEN | LAST_SEEN | clientip |
+--------+-----------+---------+----+-------------+-------+-------+---------------------+---------------------+-----------+
| 191353 | foobar.de | A | IN | 193.7.176.7 | 21599 | 1 | 2014-01-27 12:07:21 | 2014-01-27 12:07:21 | 1.1.1.1 |
+--------+-----------+---------+----+-------------+-------+-------+---------------------+---------------------+-----------+
1 row in set (0.10 sec)