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
