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.halli...@gmail.com>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
joh...@pixelated.net
