Can this query be done w/o adding another column?

--20cf300fad49ea8bf904926e409f
Content-Type: text/plain; charset=ISO-8859-1

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

--20cf300fad49ea8bf904926e409f--

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.halliday@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=gcdmg-mysql-2@m.gmane.org

Re: Can this query be done w/o adding another column?

--20cf30334fcb7da0f004926eec3a
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Oct 12, 2010 at 1:59 PM, Travis Ard 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'

--20cf30334fcb7da0f004926eec3a--

RE: Can this query be done w/o adding another column?

------=_NextPart_000_00EE_01CB6A18.5B686A00
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

Sorry, try changing the column mappings.ip to use the table aliases (m.ip
and m2.ip).



-Travis



From: Paul Halliday [mailto:paul.halliday@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 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'


------=_NextPart_000_00EE_01CB6A18.5B686A00--

Re: Can this query be done w/o adding another column?

--0016364267b3f1d5c504927d4bc8
Content-Type: text/plain; charset=ISO-8859-1

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 wrote:

> Sorry, try changing the column mappings.ip to use the table aliases (m.ip
> and m2.ip).
>
>
>
> -Travis
>
>
>
> From: Paul Halliday [mailto:paul.halliday@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
> 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

--0016364267b3f1d5c504927d4bc8--