Help with query.

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Help with query.

Hi Paul!


Paul Halliday wrote:
> I have a query (thanks to this list) that uses a join to add country
> information to an IP. It looks like this:
>=20
> 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 =3D map1.ip LEFT
> JOIN mappings AS map2 ON event.dst_ip =3D 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;
>=20
> This would return something like this:
>=20
> +-------+-------------------+--------+-------------------+-- ------+
> | 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 |
> +-------+-------------------+--------+-------------------+-- ------+
>=20
> All I am interested in is the event count for each country, in this cas=
e:
>=20
> 295 CN
> ... Other countries..

As a first step, remove the columns "src_ip" and "dst_ip" from your
query, both from the select list and from the "group by" (but not from
the join condition):

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

GROUP BY src_cc, dst_cc
ORDER BY src_cc, dst_cc ASC;

The result should be:

+-------+--------+--------+
| count | src_cc | dst_cc |
+-------+--------+--------+
| 8 | NULL | CN |
| 287 | CN | NULL |
+-------+--------+--------+

Now, you are left with two problems:

1) Your query still groups by the country codes of both source and
destination (which doesn't become obvious with your sample data, as one
of these is always shown as NULL).
For example: traffic just between three countries (each combination
occurring) would give nine rows, with each country occurring three times
as source and three times as destination.
If you want the total for the source country, you must stop grouping by
destination (and vice versa).

2) If you really want the total of source and destination (the 295 in
your example, not the 287 and 8 I expect from my version), it really
might be easiest to do this in the application; I have no idea how to do
it nicely in SQL.


HTH,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, joerg.bruehe@oracle.com
ORACLE Deutschland B.V. & Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Help with query.

On Tue, 1 Feb 2011 14:46:39 -0400
Paul Halliday wrote:

> 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;
>.....
> All I am interested in is the event count for each country, in this case:
>
> 295 CN
> ... Other countries..

try:

SELECT COUNT(*) AS count, IF(map1.cc, map1.cc, map2.cc) AS country

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 country
ORDER BY country


note, I am assuming in this query that you have either a source country or a destination country, but not both.
If both values might be set, and you need to count each, this will not work.


> Thanks!
> --
> Paul Halliday
> http://www.pintumbler.org
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=simcha@syounger.com
>


--
Simcha Younger

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org