Re: Can this query be done w/o adding another column?
After bashing at this for a while with no luck I replaced the inner with left and I got the desired result. Thanks for the help. On Tue, Oct 12, 2010 at 5:18 PM, Travis Ard travis_...@hotmail.com wrote: Sorry, try changing the column mappings.ip to use the table aliases (m.ip and m2.ip). -Travis From: Paul Halliday [mailto:paul.halli...@gmail.com] Sent: Tuesday, October 12, 2010 11:37 AM To: Travis Ard Cc: mysql@lists.mysql.com Subject: Re: Can this query be done w/o adding another column? On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard travis_...@hotmail.com wrote: You could join your mappings table twice, once on src_ip and again on dst_ip: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), m.cc as src_cc, INET_NTOA(dst_ip), m2.cc as dst_cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings m ON event.src_ip = mappings.ip INNER JOIN mappings m2 ON event.dst_ip = mappings.ip WHERE timestamp BETWEEN 2010-10-12 03:00:00 AND 2010-10-13 03:00:00 GROUP BY INET_NTOA(src_ip), m.cc, INET_NTOA(dst_ip), m2.cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 10; -Travis I get an error: ERROR 1054 (42S22): Unknown column 'mappings.ip' in 'on clause' -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org
Can this query be done w/o adding another column?
Geez, really taking advantage of the list today :). This one is a little more complicated, well, in my head anyway. Same tables as before, event and mappings. Mappings is just IP to Country info. I want to be able to join both a src and dst but the problem is the mappings table just has one ip column. My initial query looks like this: SELECT COUNT(signature) as count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), INET_NTOA(dst_ip), signature, signature_id, ip_proto FROM event WHERE timestamp BETWEEN 2010-10-12 00:00:00 AND 2010-10-13 00:00:00 GROUP BY src_ip, dst_ip, signature,ip_proto ORDER BY maxTime DESC; Which would return something like this: 2 | 2010-10-12 16:34:17 | 10.1.2.3 | 173.193.202.69 | ET P2P Vuze BT UDP Connection | 2010144 | 17 Now I want to add the country info into the mix. I have made it this far: SELECT COUNT(signature) as count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), mappings.cc, INET_NTOA(dst_ip), mappings.cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings ON event.src_ip = mappings.ip OR event.dst_ip = mappings.ip WHERE timestamp BETWEEN 2010-10-12 03:00:00 AND 2010-10-13 03:00:00 GROUP BY src_ip, dst_ip, signature,ip_proto ORDER BY maxTime DESC LIMIT 10; gives me: 2 | 2010-10-12 16:34:17 | 10.1.2.3 | US | 173.193.202.69 | US | ET P2P Vuze BT UDP Connection | 2010144 | 17 which obviously isn't right ;) but is close. I was just going to change the columns in the mappings table to have src_ip and dst_ip just duplicating the ip column but I have a nagging feeling that that probably isn't necessary. Thanks. -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org
RE: Can this query be done w/o adding another column?
You could join your mappings table twice, once on src_ip and again on dst_ip: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), m.cc as src_cc, INET_NTOA(dst_ip), m2.cc as dst_cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings m ON event.src_ip = mappings.ip INNER JOIN mappings m2 ON event.dst_ip = mappings.ip WHERE timestamp BETWEEN 2010-10-12 03:00:00 AND 2010-10-13 03:00:00 GROUP BY INET_NTOA(src_ip), m.cc, INET_NTOA(dst_ip), m2.cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 10; -Travis -Original Message- From: Paul Halliday [mailto:paul.halli...@gmail.com] Sent: Tuesday, October 12, 2010 10:49 AM To: mysql@lists.mysql.com Subject: Can this query be done w/o adding another column? Geez, really taking advantage of the list today :). This one is a little more complicated, well, in my head anyway. Same tables as before, event and mappings. Mappings is just IP to Country info. I want to be able to join both a src and dst but the problem is the mappings table just has one ip column. My initial query looks like this: SELECT COUNT(signature) as count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), INET_NTOA(dst_ip), signature, signature_id, ip_proto FROM event WHERE timestamp BETWEEN 2010-10-12 00:00:00 AND 2010-10-13 00:00:00 GROUP BY src_ip, dst_ip, signature,ip_proto ORDER BY maxTime DESC; Which would return something like this: 2 | 2010-10-12 16:34:17 | 10.1.2.3 | 173.193.202.69 | ET P2P Vuze BT UDP Connection | 2010144 | 17 Now I want to add the country info into the mix. I have made it this far: SELECT COUNT(signature) as count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), mappings.cc, INET_NTOA(dst_ip), mappings.cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings ON event.src_ip = mappings.ip OR event.dst_ip = mappings.ip WHERE timestamp BETWEEN 2010-10-12 03:00:00 AND 2010-10-13 03:00:00 GROUP BY src_ip, dst_ip, signature,ip_proto ORDER BY maxTime DESC LIMIT 10; gives me: 2 | 2010-10-12 16:34:17 | 10.1.2.3 | US | 173.193.202.69 | US | ET P2P Vuze BT UDP Connection | 2010144 | 17 which obviously isn't right ;) but is close. I was just going to change the columns in the mappings table to have src_ip and dst_ip just duplicating the ip column but I have a nagging feeling that that probably isn't necessary. Thanks. -- Paul Halliday Ideation | Individualization | Learner | Achiever | Analytical http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can this query be done w/o adding another column?
On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard travis_...@hotmail.com wrote: You could join your mappings table twice, once on src_ip and again on dst_ip: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), m.cc as src_cc, INET_NTOA(dst_ip), m2.cc as dst_cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings m ON event.src_ip = mappings.ip INNER JOIN mappings m2 ON event.dst_ip = mappings.ip WHERE timestamp BETWEEN 2010-10-12 03:00:00 AND 2010-10-13 03:00:00 GROUP BY INET_NTOA(src_ip), m.cc, INET_NTOA(dst_ip), m2.cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 10; -Travis I get an error: ERROR 1054 (42S22): Unknown column 'mappings.ip' in 'on clause'
RE: Can this query be done w/o adding another column?
Sorry, try changing the column mappings.ip to use the table aliases (m.ip and m2.ip). -Travis From: Paul Halliday [mailto:paul.halli...@gmail.com] Sent: Tuesday, October 12, 2010 11:37 AM To: Travis Ard Cc: mysql@lists.mysql.com Subject: Re: Can this query be done w/o adding another column? On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard travis_...@hotmail.com wrote: You could join your mappings table twice, once on src_ip and again on dst_ip: SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip), m.cc as src_cc, INET_NTOA(dst_ip), m2.cc as dst_cc, signature, signature_id, ip_proto FROM event INNER JOIN mappings m ON event.src_ip = mappings.ip INNER JOIN mappings m2 ON event.dst_ip = mappings.ip WHERE timestamp BETWEEN 2010-10-12 03:00:00 AND 2010-10-13 03:00:00 GROUP BY INET_NTOA(src_ip), m.cc, INET_NTOA(dst_ip), m2.cc, signature, signature_id, ip_proto ORDER BY maxTime DESC LIMIT 10; -Travis I get an error: ERROR 1054 (42S22): Unknown column 'mappings.ip' in 'on clause'
