[Solved] IP address sorting

Discuss the spreadsheet application
Post Reply
alexmoen
Posts: 2
Joined: Wed Jul 22, 2009 4:04 pm

[Solved] IP address sorting

Post by alexmoen »

I have searched for an answer to this question throughout this forum and on the web, and haven't been able to find an answer.

I am attempting to sort a sheet by an IP address column. Obviously it isn't working properly, for all the known reasons:

Given the following list:
10.10.10.1
10.10.10.2
10.10.10.3
10.10.10.10
10.10.10.30
10.10.10.100

Sorting by OOO Calc by default would be:
10.10.10.1
10.10.10.10
10.10.10.100
10.10.10.2
10.10.10.3
10.10.10.30

A simple solution for this would be to pad each octet with leading 0's:
010.010.010.001
010.010.010.002
010.010.010.003
010.010.010.010
010.010.010.030
010.010.010.100

But I cannot find a way to implement this. I have tried to use a custom format (000"."000"."000"."000), but that does not work as advertised.

What would the proper format for padding be?

Thanks!
Last edited by Villeroy on Wed Jul 22, 2009 5:40 pm, edited 1 time in total.
Reason: tagged [Solved]
OOo 3.1.X on Mac OSx Leopard
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: IP address sorting

Post by Villeroy »

Number formats apply to numbers. Your data are text which is why they sort in alphabetical order.
Assuming your list in column A and some free column F:

Code: Select all

F1	=FIND(".";$A1)
G1	=FIND(".";$A1;F1+1)
H1	=FIND(".";$A1;G1+1)
I1	=LEFT($A1;F1-1)
J1	=MID($A1;F1+1;G1-F1-1)
K1	=MID($A1;G1+1;H1-G1-1)
L1	=MID($A1;H1+1;H1-1)
M1	=VALUE(I1)
N1	=VALUE(J1)
O1	=VALUE(K1)
P1	=VALUE(L1)
Order by numeric values in M1:P1
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
alexmoen
Posts: 2
Joined: Wed Jul 22, 2009 4:04 pm

Re: IP address sorting

Post by alexmoen »

Thanks for the quick reply Villeroy!

OK, that kind of makes sense, however, it seems that one could create custom formats for text as well....

I did get it to work this way, however, my sort function only allows up to 3 sort criteria, not 4. Not a big deal as all are in the 10.x.x.x network.

Thanks for the help!
OOo 3.1.X on Mac OSx Leopard
bobsutt
Posts: 6
Joined: Tue Jan 26, 2010 2:55 am

Re: IP address sorting

Post by bobsutt »

Instead of manipulating the human-readable presentation as a string, how about storing the decimal dotted-quad input as the 32-bit integer it represents, then providing dotted-quad as a display format for the underlying value. Then you could do numeric and logical operations (e.g. sort, AND) on the integer value, and the textual representation would Just Work.

IPV6 addresses are longer: It's a 128 bit integer, represented for human legibility as eight four-digit hexadecimal numbers separated by colons. They could be handled by a generalization of this mechanism of separating the representation from the underlying storage.
OOo 3.1.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] IP address sorting

Post by Villeroy »

bobsutt wrote:Instead of manipulating the human-readable presentation as a string, how about storing the decimal dotted-quad input as the 32-bit integer it represents, then providing dotted-quad as a display format for the underlying value. Then you could do numeric and logical operations (e.g. sort, AND) on the integer value, and the textual representation would Just Work.

IPV6 addresses are longer: It's a 128 bit integer, represented for human legibility as eight four-digit hexadecimal numbers separated by colons. They could be handled by a generalization of this mechanism of separating the representation from the underlying storage.
Indeed, storing 4 numbers is the way to go.
Easier than my formula approach, Data>Text To Columns... can split up dot-separated IPv4 into 4 numbers.
The latest versions support stable sorting, so you can order by more than 3 columns. Order by 1,2,3, then by column 4, which will keep the previous order within the last sort order.
Using a database, all this should be no problem as far as the database tool is able to split a string into 4 numbers. Base can connect to log files, Base queries can deynamically convert, filter and sort the required information on the fly.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
bobsutt
Posts: 6
Joined: Tue Jan 26, 2010 2:55 am

Re: IP address sorting

Post by bobsutt »

No, I'm suggesting storing the address as one number in one cell, not as four numbers in four cells, which is almost as bad as four strings in four cells. An IPV4 address, as seen in each packet that traverses the Internet, is a 32-bit integer. The conventional dotted-quad representation views that single integer as four bytes, each containing a value 0..255. It displays that value in decimal, and separates the four bytes with period characters.

For efficiency of manipulation in a spreadsheet, that address should be stored as an integer, and the dotted-quad should be just a display format. Think of the way a date is stored in a cell as a binary number. That binary number is easy for a spreadsheet to sort, do date math, etc. The user selects whether she wants to see it as "01/25/2010" or "25 enero 2010" or "20100125", but that display representation is unrelated to the underlying storage encoding.

This same abstraction of display representation from storage format will serve just as well for IPV6 addresses.
OOo 3.1.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] IP address sorting

Post by Villeroy »

I see, 255.255.255.255 = FFFFFFFF = 4294967295 = (2^32)-1

I guess that 99% of all IPs in spreadsheets come from plain text logs or HTML with human readable dot-notation.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
bobsutt
Posts: 6
Joined: Tue Jan 26, 2010 2:55 am

Re: IP address sorting

Post by bobsutt »

Yes, exactly, and that's the problem I'm trying to solve. I get system activity log data as csv text, of which one column is a date in the format "yyyymmdd" and another column is an IPV4 address in the textual format "1.2.3.4". It would be tremendously helpful if Calc could import the textual dotted-quad representation, and store it as a single 32-bit integer. Calc already knows how to parse and import and store a date, or a number; now how about an IP address?

And that same method is just as helpful for an IPV6 address in the textual format "0123:4567:89ab:cdef", which I would like to store and manipulate as a single 128-bit integer.

Not just sorting, other manipulations too - like bitwise logical masking for non-byte-aligned subnet boundaries or CIDR blocks, range math, all sorts of stuff. I want to have the same flexibility with IP addresses we currently enjoy with date/time. I doubt this can be done conveniently with user macros; it needs to be an underlying feature of the software. Are these storage/display features already part of Calc, or perhaps an addon? (I can't find them.) Where can I make a feature request? I'm a network guy, not a spreadsheet coder :-)
OOo 3.1.1 on Windows 7
greebo
Posts: 1
Joined: Tue Feb 23, 2010 10:29 am

Re: IP address sorting

Post by greebo »

bobsutt wrote: Are these storage/display features already part of Calc, or perhaps an addon? (I can't find them.) Where can I make a feature request? I'm a network guy, not a spreadsheet coder :-)
same here. with ipv4 space running out, it is nice find any "holes" you might have in iP assignements...
OpenOffice.Org v3.2.0 Windows 7
bobsutt
Posts: 6
Joined: Tue Jan 26, 2010 2:55 am

Re: IP address sorting

Post by bobsutt »

Should have un-"solved" the subject line.
OOo 3.1.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: IP address sorting

Post by Villeroy »

bobsutt wrote:Should have un-"solved" the subject line.
Why? There are several way to do this. I demonstrated how to split up the decimal notation by means of formulas. Others prefer the text-to-columns tool.
I always recommend to do all csv stuff using the Base component. It should be possible to solve this in one simple SQL query (split, convert to integer and sort).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
bobsutt
Posts: 6
Joined: Tue Jan 26, 2010 2:55 am

Re: IP address sorting

Post by bobsutt »

What is the process for making a feature request for new underlying storage types (one for 32-bit IPv4 and one for 128-bit IPv6), and for corresponding new display representations and textual input converters?
OOo 3.1.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] IP address sorting

Post by Villeroy »

Changing the underlying storage types of the spreadsheet? This will never happen. It would break the ODF standard and all compatibility with other spreadsheets for the single purpose to handle IP addresses more conveniently.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] IP address sorting

Post by acknak »

You can already store as many bits as you want--as a string. You just need some functions for doing simple math on them.

I believe there is already an extended precision extension for Calc; it might be possible to add a few functions onto that.
AOO4/LO5 • Linux • Fedora 23
gr8whitey
Posts: 1
Joined: Thu Apr 22, 2010 9:01 pm

Re: [Solved] IP address sorting

Post by gr8whitey »

Villeroy wrote:Changing the underlying storage types of the spreadsheet? This will never happen. It would break the ODF standard and all compatibility with other spreadsheets for the single purpose to handle IP addresses more conveniently.
I don't think anyone is suggesting a new storage type. It could simply be a special format for a 32 bit integer or possible some special handling of a text format. I'm not familiar with the ODF spec but I would think it would allow for attaching metadata to a cell (formatting etc.) that would allow from some sort of backward compatibility when a specific format specifier isn't recognized. I would think that compatibility with other formats could be achieved by degrading to strings (like what happens when you save formulas to CSV). I'm doing my best to never convert to xls anyway ;)

I'm trying to do some simple math on IP addresses and it's quite difficult. Having to split the addresses to several columns to maintain both readability and some sort of recognized number format is very cumbersome. I was hoping that someone had written some sort of plugin to handle IPs more gracefully (especially since OO users tend to be a bit more technical)... maybe if I get some free time I'll write one myself :)
OpenOffice 3.1.0 on Mac OS 10.6
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] IP address sorting

Post by acknak »

At least one request is on file for this: Issue 96201: ip2long() builtin ip address conversion function. There is a web address there for a helper function, although I can't say if it will be of any use.

You can register there and add your vote (up to two) or comment.
AOO4/LO5 • Linux • Fedora 23
bobsutt
Posts: 6
Joined: Tue Jan 26, 2010 2:55 am

Re: [Solved] IP address sorting

Post by bobsutt »

A cow-orker pointed out that perhaps the best way to address (sorry) this in the short term is to use the MySQL INET_ATON/INET_NTOA functions. Not exactly optimal for a spreadsheet, and doesn't yet solve the IPv6 problem, but it works.
OOo 3.1.1 on Windows 7
CTAC
Posts: 2
Joined: Wed Mar 15, 2017 2:09 pm

Re: [Solved] IP address sorting

Post by CTAC »

I've just combined all formulas from the 1st reply, enjoy:

=VALUE(LEFT($B1;FIND(".";$B1)-1))*1000000000+MID($B1;FIND(".";$B1)+1;FIND(".";$B1;FIND(".";$B1)+1)-FIND(".";$B1)-1)*1000000 +VALUE(MID($B1;FIND(".";$B1;FIND(".";$B1)+1)+1;FIND(".";$B1;FIND(".";$B1;FIND(".";$B1)+1)+1)-FIND(".";$B1;FIND(".";$B1)+1)-1))*1000 +VALUE(MID($B1;FIND(".";$B1;FIND(".";$B1;FIND(".";$B1)+1)+1)+1;FIND(".";$B1;FIND(".";$B1;FIND(".";$B1)+1)+1)-1))
LibreOffice 5.1.3.2 on MacOS 10.12.3
User avatar
Villeroy
Volunteer
Posts: 31345
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] IP address sorting

Post by Villeroy »

bobsutt wrote:A cow-orker pointed out that perhaps the best way to address (sorry) this in the short term is to use the MySQL INET_ATON/INET_NTOA functions. Not exactly optimal for a spreadsheet, and doesn't yet solve the IPv6 problem, but it works.
This office suite supports MySQL via JDBC, ODBC and with its own SDBC driver and you can integrate database data and query results seamlessly with spreadsheets
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
karolus
Volunteer
Posts: 1227
Joined: Sat Jul 02, 2011 9:47 am

Re: [Solved] IP address sorting

Post by karolus »

Hallo

@CTAG:
The original Post is quite old from 2009, its about sorting textual Representations of IP-addresses

It is NOT about high-level "Formula-obfuscation"

(meanwhile Libreoffice is able to do the expected Sort with Option: enable natural Sort)
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 24.8… flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
CTAC
Posts: 2
Joined: Wed Mar 15, 2017 2:09 pm

Re: [Solved] IP address sorting

Post by CTAC »

with this formula I've just sort bunch of ip-addresses in human-readable format. Because this thread is shown in google in top 5 results for "libreoffice ip-address sort", I've decided to share my work (based on 1st answer, though) for future visitors - it can be easily cut-n-pasted in any (probably - temporary) worksheet without any difficulties.

And yes, it is sort of necroposting, definitely )
LibreOffice 5.1.3.2 on MacOS 10.12.3
Post Reply