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

[Stewart James <Stewart.James () VU EDU AU>]

Hi,

If you want it to be an ongoing reporting system... and have control
over the software generating the SQL inserts/updates... I would update
that insert to include an additional column "base domain", then use that
for your joins. Of course a list of definitive TLDs is useful too:
http://publicsuffix.org/list/ has links to a list that seems to be
managed by Mozilla. 

You could also do the stripping to domain name when reporting... Get
record from DB strip to domain name then check data from malware
domains.

Cheers,

Stewart :)

> -----Original Message-----
> From: The EDUCAUSE Security Constituent Group Listserv
> [mailto:SECURITY () LISTSERV EDUCAUSE EDU] On Behalf Of Russell Fulton
> Sent: Friday, 12 February 2010 1:01 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.


This email, including any attachment, is intended solely for the use of the intended recipient. It is confidential and 
may contain personal information or be subject to legal professional privilege. If you are not the intended recipient 
any use, disclosure, reproduction or storage of it is unauthorised. If you have received this email in error, please 
advise the sender via return email and delete it from your system immediately. Victoria University does not warrant 
that this email is free from viruses or defects and accepts no liability for any damage caused by such viruses or 
defects.