Re: Need SQL guru to help match DNS data to malware domains

[Randall C Grimshaw <rgrimsha () SYR EDU>]

This is a mysql built-in string function

SUBSTRING_INDEX('www.mysql.com', '.', -2);

it will return only the base domain from a fqdn of any length

you could use it in statements such as SUBSTRING_INDEX('$FQDN', '.', -2) = '$BASEDN;

you could even index on the expression to optimize the relationship of the join 


Randall Grimshaw rgrimsha () syr edu
________________________________________
From: The EDUCAUSE Security Constituent Group Listserv [SECURITY () LISTSERV EDUCAUSE EDU] On Behalf Of Russell Fulton 
[r.fulton () AUCKLAND AC NZ]
Sent: Thursday, February 11, 2010 9:00 PM
To: SECURITY () LISTSERV EDUCAUSE EDU
Subject: [SECURITY] Need SQL guru to help match DNS data to malware domains

Hi

We have a database table storing data from dns queries:

+----------+------------------+------+-----+-------------------+-------+
| Field    | Type             | Null | Key | Default           | Extra |
+----------+------------------+------+-----+-------------------+-------+
| last     | timestamp        | NO   | MUL | CURRENT_TIMESTAMP |       |
| hostname | varchar(255)     | NO   | PRI |                   |       |
| ip       | int(10) unsigned | NO   | PRI | 0                 |       |
| client   | int(10) unsigned | NO   |     | 0                 |       |
+----------+------------------+------+-----+-------------------+-------+

where hostname is the hostname that was looked up in the query, ip is the associated ip and client is the ip of the 
machine that did the look up.  Last is the last time we saw this host queried.

I am wanting to match this table against the list of malware domains (www.malwaredomains.com) but there is a catch. The 
malwaredomains  are domains, not host names so one can't simply join the tables.

Anyone have any idea on how to do this efficiently.

What I want is a report of client IPs that have looked up anything within any of the malware domains in the last hour.

Russell.