1

I found this question in regards to grouping a MySQL search by a pattern, but I'm in need of doing this for IP addresses. I need to group IP addresses by the first 3 octets, but I don't quite see how to accompish this.

Thanks

Community
  • 1
  • 1
Larry G. Wapnitsky
  • 1,216
  • 2
  • 16
  • 35
  • Can you provide an example of the output you are trying to achieve? Do you want just a list of unique first 3 octets, or are you after something else? – Tim Apr 18 '12 at 16:00
  • @Tim - yes, a list of the first 3 octets for all IP address along with a count of each. – Larry G. Wapnitsky Apr 18 '12 at 16:04

1 Answers1

2

The MySQL SUBSTRING_INDEX function might be useful here:

drop table if exists test_ip;

create table test_ip
(id int unsigned not null primary key auto_increment,
ip varchar(50) not null,
UNIQUE KEY test_ip_uidx1 (ip));

insert into test_ip (ip) values ('24.21.114.4');
insert into test_ip (ip) values ('24.21.114.5');
insert into test_ip (ip) values ('24.21.114.6');
insert into test_ip (ip) values ('24.21.115.6');
insert into test_ip (ip) values ('24.21.115.7');
insert into test_ip (ip) values ('24.21.115.8');
insert into test_ip (ip) values ('24.21.116.1');

select substring_index(ti.ip,'.',3) as firstThreeOctet,count(*) as ipCount
from test_ip ti
group by substring_index(ti.ip,'.',3);

Hope it helps.

Tom Mac
  • 9,693
  • 3
  • 25
  • 35