GROUP BY IP Address Range

[Brent]

I store IP addresses using the INET_ATON function, e.g.

INSERT INTO myTable(IP_Address) Values (INET_ATON(123.123.33.12));

I would like to create a daily report that groups IP addresses by a "dotted
triad", e.g.

(pseudo SQL) SELECT myTable.IP_Address FROM myTable GROUP BY
123.123.33.*;

Any ideas on the best way to go about this?

Thanks for your help!

--Brent

[steve]

"Brent" wrote:

I store IP addresses using the INET_ATON function, e.g.

INSERT INTO myTable(IP_Address) Values
(INET_ATON(123.123.33.12));

I would like to create a daily report that groups IP addresses by a
"dotted
triad", e.g.

(pseudo SQL) SELECT myTable.IP_Address FROM myTable GROUP BY
123.123.33.*;

Any ideas on the best way to go about this?

Thanks for your help!

--Brent

What is INET_ATON?

If you want to do serious IP range reporting, then I suggest to split
up the ip into its 4 components, and then save each of the 4
components to a single field in your db. So ’123.111.98.11" would
be saved as:
123 , 111, 98, 11

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-GROUP-...ict132947.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=443943

[Isaac Blank]

Not sure whether you need GROUP BY or ORDER BY, but to get the "dotted
triad", you can use the following expression:

LEFT(IP_Address , LENGT(IP_Address) - LOCATE('.', REVERSE(IP_Address)))
"Brent" <bbigler @ ahem-'whoopee' . com> wrote in message
news:aw*******************@newssvr29.news.prodigy. com...

I store IP addresses using the INET_ATON function, e.g.

INSERT INTO myTable(IP_Address) Values (INET_ATON(123.123.33.12));

I would like to create a daily report that groups IP addresses by a "dotted triad", e.g.

(pseudo SQL) SELECT myTable.IP_Address FROM myTable GROUP BY
123.123.33.*;

Any ideas on the best way to go about this?

Thanks for your help!

--Brent

[Isaac Blank]

Not sure whether you need GROUP BY or ORDER BY, but to get the "dotted
triad", you can use the following expression:

LEFT(IP_Address , LENGT(IP_Address) - LOCATE('.', REVERSE(IP_Address)))
"Brent" <bbigler @ ahem-'whoopee' . com> wrote in message
news:aw*******************@newssvr29.news.prodigy. com...

I store IP addresses using the INET_ATON function, e.g.

INSERT INTO myTable(IP_Address) Values (INET_ATON(123.123.33.12));

I would like to create a daily report that groups IP addresses by a "dotted triad", e.g.

(pseudo SQL) SELECT myTable.IP_Address FROM myTable GROUP BY
123.123.33.*;

Any ideas on the best way to go about this?

Thanks for your help!

--Brent