List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:February 1 2011 6:46pm
Subject:Help with query.
View as plain text  
I have a query (thanks to this list) that uses a join to add country
information to an IP. It looks like this:

SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as
src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc
FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT
JOIN mappings AS map2 ON event.dst_ip = map2.ip
WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00'
GROUP BY src_ip, src_cc, dst_ip, dst_cc
ORDER BY src_cc, dst_cc ASC;

This would return something like this:

 +-------+-------------------+--------+-------------------+--------+
| count | INET_NTOA(src_ip) | src_cc | INET_NTOA(dst_ip) | dst_cc |
+-------+-------------------+--------+-------------------+--------+
|     8 | 10.0.0.8       | NULL   | 61.55.142.129     | CN     |
|     1 | 210.52.216.92     | CN     | 10.0.0.2         | NULL   |
|     1 | 121.33.205.235    | CN     | 172.16.0.6        | NULL   |
|   239 | 210.52.216.92     | CN     | 10.0.0.2        | NULL   |
|     2 | 121.33.205.235    | CN     | 172.16.0.15       | NULL   |
|     4 | 121.33.205.235    | CN     | 10.0.0.1        | NULL   |
|    39 | 210.52.216.92     | CN     | 172.16.0.15       | NULL   |
|     1 | 121.33.205.235    | CN     | 172.16.0.14       | NULL   |
+-------+-------------------+--------+-------------------+--------+

All I am interested in is the event count for each country, in this case:

295 CN
... Other countries..

I can do this in code, more work of course, but I am just curious if I
can pull it off with a single query.

Thanks!
-- 
Paul Halliday
http://www.pintumbler.org
Thread
Help with query.Paul Halliday1 Feb
  • Re: Help with query.Joerg Bruehe2 Feb
  • Re: Help with query.Simcha Younger2 Feb