How to group IP list by subnet in MySQL?

Question

I need to group the IP list by subnet (first 3 octets) in order to count it. For example, if I have Ips

123.12.12.12 123.12.12.11 123.12.11.11 

I need to get this result:

 123.12.12 | 2 123.12.11 | 1 

I was looking for this example:

 select substr(ip,1,locate('.',ip,locate('.',ip)+1)-1) as ip, count(ip) as count from ip_list group by ip ORDER BY count DESC 

But he only groups the list by the first two octets. I got lost in all of these locate(locate(locate(...))) . Can someone help change this to get the right results?

Answer 1

You must use a group by expression.

 select -- locate( '.', ip, locate( '.', ip, locate( '.', ip ) + 1 ) + 1 ) as l, substr( ip, 1, locate( '.', ip , locate( '.', ip , locate( '.', ip ) + 1 ) + 1 ) - 1 ) as subip, count(ip) as count from ip_list group by ( subip ) order by count desc ; 

EDIT 1 :
Using locate not required. SUBSTR_INDEX can be used to filter a subset of IP addresses.

An example :

 select substring_index( ip, '.', 3 ) as subip , count(ip) as count from ip_list group by ( subip ) order by count desc 

Refer to the documentation :

• MySQL: SUBSTR (str, pos, len)
  • Returns a substring of len characters long from the string str, starting at position pos.
• MySQL: SUBSTRING_INDEX (str, delim, count)
  • Returns the substring from the string str before counting the occurrence of the delimiter.

Answer 2

Using this logic, I'm afraid that you will need another locate layer:

 substr(ip, 1, locate('.', ip, locate('.', ip, locate('.', ip)+1) +1) - 1) 

Answer 3

you can use

 GROUP BY INET_ATON(ip)/256 

Answer 4

If you create a binary IP file, you can apply a mask to it that saves only the first 3 parts of your IP address. The mask looks like this:

 11111111111111111111111100000000 

So, in the form of numbers, it looks like this:

 SELECT CONV('11111111111111111111111100000000' ,2,10) as mask; -- Result: 4294967040 

If you perform bitwise AND at your IP address using this mask, you will get this result:

 SELECT '1.0.207.199', INET_NTOA( INET_ATON( '1.0.207.199') & 4294967040 ) ; -- Result: -- 1.0.207.199 -- 1.0.207.0 

INET_ATON converts ip (A) ddress (TO) a (N) umber and INET_NTOA vice versa (although the real value means ASCII TO Network, but I like this shofthand better ;-).

So you can group your IP addresses as follows:

 SELECT INET_NTOA( INET_ATON( ip ) & 4294967040 ) AS ipgroup, COUNT(ip) AS count FROM ip_list GROUP BY ipgroup ORDER BY count DESC; 

Hope this helps.