May 272014
 

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)