List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:October 12 2010 2:14pm
Subject:Re: Can this be done with a single query?
View as plain text  
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.

-JW


On Tue, Oct 12, 2010 at 5:19 AM, Paul Halliday <paul.halliday@stripped>wrote:

> I have 2 tables: events and mappings.
>
> what I want to do is something like:
>
> SELECT DISTINCT(src_ip) FROM event WHERE timestamp BETWEEN '2010-10-11
> 00:00:00' AND '2010-10-12 00:00:00' 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.%.%';
>
> but, within that somewhere also check to see if src_ip exists in mappings.
> If it does, do not return it.
>
> Is this possible?
>
> Thanks.
> --
> Paul Halliday
> Ideation | Individualization | Learner | Achiever | Analytical
> http://www.pintumbler.org
>



-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

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