I.NFECTIO.US

The Problem

An IP Address in its dotted-quad format will not sort properly. For example, 192.0.0.0 will show before 69.0.0.0 when sorted in ascending order (1 comes before 6 right?). So we need a way to have SharePoint sort on this column.

The Solution

To properly sort an IP address, you must first convert it into an integer. Doing this converts 192.168.0.1 into 3232235521 and 69.0.0.0 into 1157627904. Now when you sort the IP, it will correctly order them based on the value of the integer. To accomplish this in SharePoint, you have two options and I will cover both of them. The first is to create a hidden calculated field on your list and use a formula to convert the IP into an integer. Then, by creating views that sort by the hidden field, you can display the sorted IPs.

Create a calculated field to sort IPs

Create a new field named IPAddress and leave it as a single line of text. Add another field named SortableIPAddress and make it a calculated field. Now add the following formula (warning: its not pretty).

=VALUE(IF(NOT(ISBLANK([IPAddress])),(LEFT([IPAddress],
FIND(".",[IPAddress],1)-1)*16777216)+(MID([IPAddress],
FIND(".",[IPAddress],1)+1,FIND(".",[IPAddress],
FIND(".",[IPAddress],1)+1)-FIND(".",[IPAddress],1)-1)*65536)+(MID([IPAddress],
FIND(".",[IPAddress],FIND(".",[IPAddress],1)+1)+1,
FIND(".",[IPAddress],FIND(".",[IPAddress],
FIND(".",[IPAddress],1)+1)+1)-FIND(".",[IPAddress],
FIND(".",[IPAddress],1)+1)-1)*256)+(RIGHT([IPAddress],
LEN([IPAddress])-FIND(".",[IPAddress],
FIND(".",[IPAddress],FIND(".",[IPAddress],1)+1)+1))),0))

Once you add this calculated field, create a new view that sorts on the hidden SortableIPAddress field.

Now lets test our work. First we add some IPs out of order and see that they are displayed in the order they were input.

And when we switch to the sorted view, we can see that it orders them properly. Ordinarily, you wouldn't have the SortableIPAddress field visible in your view. It's shown here as an example.

This is the easy way, being a sucker for punishment, I decided to go the hard way and create a custom field type to handle this internally. Thats next after this SharePoint Tip on field naming.

SharePoint Tip #3241

Never name anything in SharePoint with spaces during creation. After creation, edit the name and add your spaces. Then you'll never have to deal with the IP_x0020_Address problem.

Create a custom field type to sort IPs

Custom field types in SharePoint are great as long as you keep them simple. The API needs a lot of refactoring to make it a robust mechanism for extending SharePoint and more complete documentation wouldn't hurt either. Here's the setup, we want a custom field that allows you to enter an IP in dotted-quad format, validate it, store it as an integer (which makes it sortable) and display it back in dotted-quad format. Easy right? Actually, it's not too difficult, but its not a perfect solution either as you'll see.

I'm not going to cover the details of creating a Custom Field Type project in Visual Studio. The important points for the IPAddress field type are the conversions from dotted-quad to long and the rendering of the IP in the different views. Since we want to make sure we have valid IP addresses to begin with, we'll borrow the idea and code from Todd Bleekers book (Developer's Guide to Windows SharePoint Services 3.0) for a Regular Expression based validated field. By overriding the GetValidatedString method of the SPField class, we are able to validate and convert the dotted IP into it's numeric format.

public override string GetValidatedString(object value) {
	string ipaddress = value.ToString();
	if (!string.IsNullOrEmpty(ipaddress)) {
		if (!ValidIp(ipaddress)) {
			throw new SPFieldValidationException("Invalid IP Address format detected.");
		}
		return IpToLong(ipaddress).ToString();
	}
	// We defer to base to use its Required field validation
	return base.GetValidatedString(value);
}

And the Valid IP method just uses a regular expression to match the 0.0.0.0-255.255.255.255 range.

private bool ValidIp(string ipaddress) {
	string regExStr = @"^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$";
	Regex reg = new Regex(regExStr, RegexOptions.IgnoreCase);
	return reg.IsMatch(ipaddress);
}

These methods make sure we have a valid IP in dotted-quad format, converts it into the numeric format and ultimately stores it in the database. Now we need to reverse it when we display it to the user. SharePoint offers several methods to alter the display of the data in an SPField object. I've chosen to just override the GetFieldValueForEdit method to convert the stored value on the Edit Page and a RenderPattern for Display which we'll discuss below.

public override string GetFieldValueForEdit(object value) {
	string ShouldBeIP = value as String;
	if (!string.IsNullOrEmpty(ShouldBeIP)) {
		return LongToIp(ShouldBeIP);
	}
	return ShouldBeIP;
}

At this point, we have a validated input field for dotted IP addresses that are stored in the database as integers (which allows us to sort them) and displays the dotted IP to the user when editing the value. The AllItems and Display page don't retrieve the value for a list item using the GetFieldValueForEdit method. Instead, I use some javascript in a RenderPattern in the fldtypes*.xml file. Seems like time for another SharePoint tip brought to you by Inside Windows SharePoint Services 3.0 written by Ted Pattison and Daniel Larson, if you don't own it, you're probably not a SharePoint developer.

SharePoint Tip #911

If you find yourself enjoying CAML, seek professional help immediately.

If you've developed any custom field types, then you are already familiar with the Field Type Definition file located in TEMPLATE/XML/fldtypes*.xml. Here is the display render pattern I used to convert the numeric IP into a dotted decimal version for viewing in lists. It's broken down into separate lines here, but needs to be all on one line in the actual file.

<HTML><![CDATA[<SCRIPT>var ipnumber=]]></HTML>
<Column/>
<HTML><![CDATA[;document.write(
Math.floor(ipnumber/16777216)%256 + '.'
+ Math.floor(ipnumber/65536)%256 + '.'
+ Math.floor(ipnumber/256)%256 + '.'
+ Math.floor(ipnumber)%256);</SCRIPT>]]>
</HTML>

And now we acheived the same thing as the calculated column above but without having to have 2 fields.

There is one small gotcha that I haven't worked around yet. Sorting and everything works, but when you open the sort menu, it displays the IPs in their numeric form. Unfortunately, SharePoint doesn't seem to chose only one method to grab the values from the database. This is a small enough gotcha that I won't worry about it for now. If you can figure it out, please let me know so I can update the code.

You can download the source for this Custom Field here and if you just want to use it, you can download a solution file to install it.