List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:October 12 2010 4:08pm
Subject:Re: Can this be done with a single query?
View as plain text  
On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers <johnny@stripped>wrote:

> I would try:
>
> SELECT DISTINCT(e.src_ip)
> FROM event AS e
> LEFT JOIN mappings AS m ON e.src_ip=m.src_ip
> WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00'
>  AND INET_NTOA(e.src_ip) NOT LIKE '10.%.%.%'
> AND INET_NTOA(e.src_ip) NOT LIKE '172.16.%.%'
> AND INET_NTOA(e.src_ip) NOT LIKE '192.168.%.%'
> AND m.src_ip IS NULL
> ;
>
> I would also modify the where clause to use:
>
> AND src_ip NOT BETWEEN INET_ATON('10.0.0.0') AND INET_ATON(10.255.255.255)
> AND src_ip NOT BETWEEN INET_ATON('172.16.0.0') AND
> INET_ATON(172.16.255.255)
> AND src_ip NOT BETWEEN INET_ATON('192.168.0.0') AND
> INET_ATON(192.168.255.255)
>
> instead of
>
> AND INET_NTOA(src_ip) NOT LIKE '10.%.%.%'
> AND INET_NTOA(src_ip) NOT LIKE '172.16.%.%'
> AND INET_NTOA(src_ip) NOT LIKE '192.168.%.%
>
> You should also ensure there is an index on src_ip in events and mappings
> tables.
>
> Using the INET_NTOA() function on the src_ip column will prevent index
> usage during the query.
>

This and the suggestion by Nathan both work.

Thanks for the help!

Thread
Can this be done with a single query?Paul Halliday12 Oct
  • Re: Can this be done with a single query?Nathan Sullivan12 Oct
  • Re: Can this be done with a single query?Johnny Withers12 Oct
    • Re: Can this be done with a single query?Paul Halliday12 Oct
      • RE: Can this be done with a single query?Travis Ard12 Oct
        • RE: Can this be done with a single query?Daevid Vincent13 Oct