Good to be the geek
There are moments when I have to sit back and enjoy being a geek. Today had one of those moments. After an upgrade the library system developed a new quirk. It was almost impossible to get phone numbers to print on the notices we use to call people when an item they have placed a hold on arrives. I spoke with the vendor at length about the problem and was told that because we had an old “PHONE” field in all of the older user records (all staff, faculty and admins) there was not much that could be done. The system runs through the list of phone fields and the first field it encounters with the word phone in it prints. That is unless that 1st field is blank, then it prints nothing at all.
I asked the company if it was possible to delete that field in records where it was empty. They gave us three options. Option one was to manually remove that field from each user record. To remove the field twice from over 3500 users would be a daunting task to say the least. For option two they said they could write a program to fix it for us but it would cost a rather large sum of money. Option three almost seemed offered in jest. It was the option where I could write the program myself. None of the options provided seemed all that appealing to me.
After letting a co-worker start on the list for a day or so, I began to tinker with the process as well. It was extremely tedious. I hate tedium. So I started to consign myself to the fact that I would once again have to delve into the world of SIRSI API. Believe me when I say, it is unlike anything most people would consider an API. I ran through my API manual to no avail, the commands I wanted were not listed. I went through the electronic documentation, still no help. I skimmed the super secret API guru website, and saw nothing of use for this problem.
It was at that point that I decided I was on my own. I began watching the transaction logs on the server as I manually removed field after field in user record after record. Each time I removed a field a specific line of data appeared in the log. It seemed a little to easy at first glance, that was of course because it was to easy. The logs only showed the second half of a two part process. In order to delete a field, you have to know the fields “Absolute Field Position”. This is a number that for my purposes might as well have been random. (It’s not really random of course, but it might as well have been) I had hit a brick wall so to speak. How was I going to find out the number I needed when the command used to retrieve the number was purposefully filtered out of the logs, and left out of the documentation. I remembered from my training that there was a way to un-filter a command, but to do so I would need to know what command it was I needed un-filtered. So there was no real help there.
I finally had a burst of inspiration this afternoon while explaining the problem to my co-worker / friend, Kendall. I tend to get those bursts of inspiration around him. I chalk it up as some form of a Holmes and Watson relationship. He forces me to come at the problem from different angles, and that often leads me to the solution. Today was no exception. I hit upon the idea of creating a logging proxy that would sit between the client and the server and dump all of their communications to a log file of my own. I could then read my log file, which was not filtered, and had the advantage of showing traffic both directions. I already knew the 2nd half of the sequence to delete, so then logically what came before it in that log would be the missing link I was looking for. Thankfully there is no need to actually create such a proxy from scratch. The gurus of the Unix/Linux world have already done that in a program called “netcat”. After a little tinkering with netcat I had a proxy setup. I connected to the server via the proxy and deleted my first field. A quick glance into my log file and I was presented with a massive burst of data from the server in response to a previously unknown command. I had indeed found my missing command.
Now that I had the command it became a simple matter of using it to retrieve the “Absolute Field Position”. Once I had that number I was able to construct the second part of the command. After doing a little fidgeting in a text editor I manually sent the newly constructed command into the server and nothing happened. Well actually something happened, it gave me an error. I tinkered and tweaked and finally noticed the problem. Once I corrected my mistake I ran my command again and to my cheers of joy the old PHONE field vanished from the users record. One record down ~6999 to go. By this time it was 4:15 and I quickly documented all of my progress and dumped it to my jump drive for safe keeping. When I arrived at home I found myself driven to complete the project and actually write code to automate this process.
I tinkered and coded and coded and tinkered and around 8:00 I ran my first test. It was a success. A little more tinkering and I am now happy to report that by 9:15 tonight all of those troublesome fields are just a distant memory.
