List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:February 2 2011 11:47am
Subject:Re: Help with query.
View as plain text  
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:
> 
> 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..

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 = 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_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

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bruehe@stripped
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

Thread
Help with query.Paul Halliday1 Feb
  • Re: Help with query.Joerg Bruehe2 Feb
  • Re: Help with query.Simcha Younger2 Feb