Educause Security Discussion mailing list archives
Re: Need SQL guru to help match DNS data to malware domains
From: Stewart James <Stewart.James () VU EDU AU>
Date: Fri, 12 Feb 2010 16:25:26 +1100
Though if you do it that way you would miss potential regional approaches. For example www.vu.edu.au would yield edu.au and if you get into the AU government (as an example) domains, the states are broken down into things like vic.gov.au or nsw.gov.au I guess it really would depend on the number of domains listed my malware domains that hang right on the true TLD's. :)
-----Original Message----- From: The EDUCAUSE Security Constituent Group Listserv [mailto:SECURITY () LISTSERV EDUCAUSE EDU] On Behalf Of Randall C Grimshaw Sent: Friday, 12 February 2010 3:39 PM To: SECURITY () LISTSERV EDUCAUSE EDU Subject: Re: [SECURITY] Need SQL guru to help match DNS data to
malware
domains 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.
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.
Current thread:
- Need SQL guru to help match DNS data to malware domains Russell Fulton (Feb 11)
- <Possible follow-ups>
- Re: Need SQL guru to help match DNS data to malware domains Stewart James (Feb 11)
- Re: Need SQL guru to help match DNS data to malware domains Randall C Grimshaw (Feb 11)
- Re: Need SQL guru to help match DNS data to malware domains Stewart James (Feb 11)