Amuyu

So, I was just at a customer site today. They have an interesting storage problem. Part of it was that we needed to map a whole bunch of hosts IP addresses to their VLAN id’s so that we can determine how much storage was in each VLAN. This will help architect our backup solution. Well, there was no such simple mapping to be used, so we started with an excel spread sheet that had all of the hosts names and each of their drive letters (capacity and free space). Another excel spreadsheet had the hostname to IP mapping. Yet a third spreadsheet had the subnet mask to VLAN tag mapping. Problem was that none of these represented the data in a consistent manner. The storage spreadsheet had the short name of the hosts. The hostname+IP spreadsheet had the fully qualified domain name and the IP addresses in a full decimaled notation (i.e. 010.010.005.013 as opposed to 10.10.5.13). The VLAN spreadsheet had the domains listed in the standard x.x.x.x/x notation. Well manipulating almost all of these turned out to be mostly doable via excel. But, the conversion of 010.010.005.013 to something sensible turned out to be not so easy. Excel’s string functions are, ahhh…, rudimentary. So, rather than waste a whole bunch of time writing an excel equation that had a whole bunch of “=left(A2,search(“.”,A2,(search(“.”,A2)))” nonsense, I turned to my trusty command line on the mac and turned to sed. I copied the column with the IP’s to a text file called aa (one IP/line). It looked something like this:

slick:~$ cat aa
010.003.001.208
010.004.001.040
010.007.096.016
010.004.001.226
192.168.012.032
192.168.013.021
010.002.001.160
010.010.004.164
010.010.004.165
010.010.004.049
010.010.004.051
010.010.004.052
010.002.001.034
010.010.003.039
192.168.013.200
10.2.19.92
10.2.19.91
010.010.003.052
010.010.003.053

I then wrote this shell one liner:

slick:~$ while read i; do echo $i | sed ‘s/^0*\([0-9]*\.\)0*\([0-9]*\.\)0*\([0-9]*\.\)0*\([0-9]*$\)/\1\2\3\4/’; done <aa

and it output this:

slick:~$ while read i; do echo $i | sed ‘s/^0*\([0-9]*\.\)0*\([0-9]*\.\)0*\([0-9]*\.\)0*\([0-9]*$\)/\1\2\3\4/’; done <aa
10.3.1.208
10.4.1.40
10.7.96.16
10.4.1.226
192.168.12.32
192.168.13.21
10.2.1.160
10.10.4.164
10.10.4.165
10.10.4.49
10.10.4.51
10.10.4.52
10.2.1.34
10.10.3.39
192.168.13.200
10.2.19.92
10.2.19.91
10.10.3.52
10.10.3.53

So, the question is, can any of you (the four people that read my blog) do better on the RE in the sed statement?

Also, Microsoft, please add regular expressions to the function list! If not Steve Jobs, Numbers?

This entry was posted on Friday, February 8th, 2008 at 10:50 pm and is filed under Geekfest. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.