Network Monitoring

I’m in the middle of a rather protracted evaluation of network infrastructure monitoring software. I’ve started looking at Paessler’s PRTG, also SolarWinds Orion product and in January I’ll be looking at Ipswitch’s products.

I also started looking at Nagios and Cacti. That’s where the fun-house mirrors start. The first big hurdle is no cost vs. cost. The commercial products mentioned before are rather pricey while Nagios and Cacti are GPL, and open sourced, principally available for no cost.

With PRTG, it was an engaging evaluation however I ran into one of the first catch-22’s with network monitoring software, that Symantec Endpoint Protection considers network scanning to be provocative, and so the uneducated SEP client blocks the poller because it believes it to be a network scanner. I ran into a bit of a headache with PRTG as the web client didn’t register changes as I expected. One of the things that I have come to understand about the cost-model network products is that each one of them appears to have a custom approach to licensing. Each company approaches it differently. PRTG is based on individual sensor, Orion is based on buckets, and I can’t readily recall Ipswitches design, but I think it was based on nodes.

Many of these products seem to throw darts at the wall when it comes to their products, sometimes hit and sometimes miss. PRTG was okay, it created a bumper crop of useless alarms, Solarwinds Orion has an exceptionally annoying network discovery routine, and I haven’t uncorked Ipswitch’s product yet.

I don’t know if I want to pay for this sort of product. Also, it seems that this is one of those arrangements that if I bite on a particular product, I’ll be on a per-year budget cost treadmill for as long as I use the product unless I try the no-cost options.

This project may launch a new blog series, or not, depending on how things turn out. Looking online didn’t pan out very much. There is somewhat of a religious holy war surrounding these products. Some people champion the GPL products; other people push the solution they went with when they first decided on a product. It’s funny but now that I care about the network, I’m coming to the party rather late. At least, I don’t have to worry about the hot slag of “alpha revision software” and much of the provider space seems quite mature.

I really would like anyone who works in the IT industry to please comment with your thoughts and feelings about this category if you have any recommendations or experiences. I’m keenly aware of what I call “show-stopper” issues.

Microsoft in the Cloud

A good question comes to mind, will companies like Microsoft, unhappy with individual flashes of purchasing, like 10,000 units of Windows XP, would rather migrate their users into a cloud infrastructure that replaced these discrete purchases into a steady stream of what amounts to being rental income? It’s starting with Azure and the new CEO has said cloud is the future. This may be where they are heading.

Microsoft might try to corner their own hardware market eventually, shepherding their customers into a cloud model, where you pay for a Windows 9 experience, connecting to Microsoft’s own hardware over the Internet. It would eliminate a huge sector of headaches for Microsoft, as they have never been truly able to strictly control the hardware their operating system runs on, unlike Apple. With a VM of Windows 9 that is remote controllable, Microsoft could provide a channel for their customers to use their suite of applications, achieving a silo lock-in. Instead of selling a license to use Windows 9, Microsoft could simply sell a $50 per month lease to computing resources within Microsoft. The sales pitch and marketing could be incredibly lucrative for Microsoft. Having a virtual OS canned with every application Microsoft makes available for a certain low per-month price, and altering that price based on the performance specifications of the VM ordered, so that clerical staff who need a basic interface can come in at $20 per month and developers who need more can come in at $200 per month.

That would eliminate many hurdles for IT administrators, the client machines could be thin clients, cookie cutter boxes with very little technology in them beyond the human interface components and the network connection. Storage, the VM instances, security, antivirus, the entire ball of wax could be handled by Microsoft itself, playing host to their customers and transforming their entire model from that of a classic production model to a new cloud-based leasing model. It would likely lower their profits for a short time, but the curve would not be so choppy, it would be smooth as leasing models, while working, are steady streams of money.

The risk to this possibility comes from the shift of importance from the local hardware to remote hardware. The weak link is the network itself. Virtual machine technology plays a part in this shift of risk, when you start putting more than one egg in a basket, you really have to concentrate on making sure your basket never fails. In this case, if the network link goes down, the entire affair disappears as if it wasn’t even there. This risk could be mitigated by establishing redundant network connections or having some sort of stop-gap measure devised where a host machine is shipped and installed to perform as a surrogate until the primary system returns to function. I don’t see this being a huge risk, as the Internet was designed to be very resilient to link failures as it is. It would come down to the last-mile service provider and the electrical grid maintaining service.

It’s something that is interesting to think about. Microsoft could do this, and it could revolutionize their business model and perhaps give them an edge in the enterprise level market. Only time will tell.

One Slipped Key

Death By ChocolateWhile working I wrote a little bit of SQL, trash really because it was just a one-shot query, real short too, and I wanted to show off the SQL code for making the iModules degree info pretty. Instead of clicking open, I clicked the save button. I found the file I thought I was opening and double-clicked. The computer asked me “Are you sure you want me to save using this file, overwriting the old file?” and I absent-mindedly clicked Yes.

The little useless fragment of SQL code replaced my huge SQL script. Boom. All gone. So sorry.

So then I was thinking about how I could recover the file, that it was on my laptop at home and so if I could turn off the Wifi at home and start my laptop I could copy the file before the Dropbox sync app replaced what I needed with my mistake.

But then I thought there should be something in Dropbox that helps address my stupidity. Turns out there is. Right click on your oops file, click on “View Previous Versions” and it opens a website and shows you all the previous times you saved your file on the service. Oh look, there’s all my hard work, right there. Click. Whew!

So, how much do I love Dropbox? Even more.

 

photo by: JD Hancock

Burning Sage

Holy Pickled Pomegranate Batman !I just received my invitation to attend Sage Summit 2013 in Washington, DC from July 23rd to the 26th at Gaylord National Hotel and Resort.

Since Sage dropped the hot potatoes it was juggling, this yearly pilgrimage is now utterly laughable and irrelevant. Not only will I not go to Washington, DC in the pit of Summer but I will definitely not be going to another Gaylord property. Those “resorts”, especially the abomination in Nashville Tennessee is a crime against humanity and an insult against nature.

My “most favored thing” today that I will do is to click the Unsubscribe button to all Sage communications. My interest drops like wet trousers around the ankles of my professional disgust. Tootles!

photo by: recubejim

SQL Tattletale

While at work I had an irritation with my SQL Server database. My coworkers give me data to load and without fail I have to create new tables to accept the data that they give me. The data is all headed somewhere and that somewhere always has many tables and in each of those tables many columns. The need I have is for the names of those columns, their data types, how long they are, and what their collations are. I don’t really have any of that information written down and for the longest time I’ve been schlepping to sp_help ‘table name’ to find the answers and it’s been a right mess. The data is there, but it’s annoying as sp_help gives me way too much information and there is no way to trim it back. So, I created a new stored procedure and I named it “tattle”, here it is in T-SQL:

CREATE PROCEDURE [dbo].[tattle]
@MYARG char(255)
AS
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@MYARG
GO

I’m quite proud of what I accomplished and the output is EXACTLY what I need when I need it. To run it I just type in tattle ‘table’ and it spits out just what I am after.

It’s the little victories you have to savor. 🙂

Hawthorne

While running my SupportPress system for several months now it dawned on me that the  MySQL database that lurks just behind the scenes is collecting quite a bit of accidental detail on my coworkers that use the system. Nothing that would endanger anyones privacy, so quickly put that notion out of your head. What it does do is record the datestamp of when people enter tickets into the system. Generally people enter tickets right after they’ve identified a problem, of if they come to us, we start the ticket as the first thing we do, so again, the initiation of a ticket is in a general sense linked to just after the problem was detected and brought to our attention. For the remainder of this post, I assert that the datestamp on the ticket is when the problem occurred.

So with a middling amount of database skills and analysis under my belt I decided to run some aggregate queries on the data. Here’s what I found:

When I aggregate all the days of the month together and count how many tickets were initiated on which particular days I discover four notable days where things seem to on-average, go haywire: The 1st with 81 tickets, the 6th with 66 tickets, the 12th with 56 tickets, and the 23rd with 79 tickets. What’s really interesting about this dataset are the dates on the opposite side. The least haywire day is the 31st, with 11 tickets then the 28th with 20 tickets and the 3rd with 28 tickets. So now I can say, in general, that the 1st, the 6th, the 12th, and the 23rd are “Days That Suck”. The best day is the 31st.

I ran the same analysis but instead of days of the month, I used hours. In general the mornings are where people seem to have the most problems. 14 tickets at 7am, 201 tickets at 8am. At Noon the tickets drop by a hundred, then from 2pm to 4pm it rises gently but nowhere near the morning values and the afternoon peak comes between 4 and 5pm. After 6? The rush of tickets just stop. People don’t report problems when food is on the line. 🙂

The last analysis I did crossed day and hours together and counted the tickets. This had some interesting data in it, since I was just looking for any notable outliers. Hours and Days where the ticket level was say, more than 20 for that hour. Here’s some of the “Problem Days and Hours”: The 1st of the month at 10am and the 23rd at 10am.

What is to be determined from these really general findings? There are some cursed days at the office apparently. The 1st, the 6th, the 12th and the 23rd are really quite troublesome for people. Mornings are rough but on the 1st and 23rd, at around 10am the shit hits the fan.

SupportPress collects this data for as long as it runs and I’ve no intention of stopping so the further we go the more (or maybe less) pronounced these interesting bits of information will get. Those that work with me that also read my blog might have some insight or they may just find some of this helpful if they have spare vacation days to spend and are looking for some reason to not come into work.

Sage Summit 2012

So here I sit at BNA, which is the airport in Nashville Tennessee. It’s just after a near week-long stint at a work conference here for our database vendor, Sage. The conference went very well and I got enough out of it to feel like I got my organizations moneys worth. There were high points and low points but looking back on the entire experience the good far outweighs the bad. The next time I have to do this will be when this event moves to Washington DC on July 21st 2013.

I haven’t blogged in a while and mostly it’s because the conference and socializing pretty much grabs you right after you wake up and won’t let you go until you drop in your shoes. I got to laughing that the only real moments of privacy I had was when I was in the bathroom, only because people won’t follow you into the john to talk – thank god for that! It actually became rather tragicomic. Each time I would get ready to start working with my email, or Instapaper, or well, anything else really there would be someone who would hail me and we’d start talking. Now, don’t get me wrong, I would far prefer to be interrupted and engage, after all, that’s what I’m here for, so this isn’t a complaint. This is more of an idle observation. The only time I wasn’t engaging with peers was when I was in the bathroom. So, in a way, the bathroom is the last frontier. 🙂

As I attended sessions and get-togethers the same themes kept on appearing with an unusual regularity. That people kept on referring to their IT staff as “them” and the sense of their feelings were that their IT really didn’t understand or appreciate their needs. I’ve written about this before, about what I do in my job and that I really have never understood how other people run their shops. It has been a concern of mine that whenever I introduce people to how I run my shop I get very similar responses. It’s shock, that an IT shop is open and receptive and welcoming enough to engage with their supported staff. In many ways I suspect that these other organizations have never read the “Good Book” when it comes to running an IT shop, which is “The Practice of System and Network Administration”. There is nothing I can do for these other organizations and I’m kind of pinned at Western. During the conference it struck me that I could very well start to write about some of the philosophies that I have developed in my little tiny niche. So in the spirit of that, I will be writing more about my job and the technical things that I do in the day-to-day operation of my job.

I often times sit back and wonder how many of my work peers read any of this drivel. Much of what I write will be dry-runs for some topics I plan on presenting during the next Sage Summit in Washington, DC.

So with Nashville functionally in my past, if not positionally, the blog posts coming up next will be more about my work. I wouldn’t blame anyone if they stopped reading for a while. It’s going to be dull. 🙂

GLMUG, with the Lead Pipe, at NIU.

Just returned from a work-related mini-conference. Every year all the sites that use Sage Millennium gather together in their regions and meet with Sage representatives and talk about the database, how we use it, how we get other people to use it, and to socialize between each other. For us, we are a part of the Great Lakes Millennium Users Group, GLMUG for short. Over the years we have gotten together at WMU, where I work, and other years we have gone to St. Olaf University in Minnesota, Medical College of Wisconsin at Milwaukee, and this year Northern Illinois University.

For these events we are centrally located to pretty much every GLMUG member so for us, we pretty much always drive to wherever is selected to be our host for the meetings. We schedule a three day mini-conference, with the first day occupied by landing and socializing, then we dive into topics the next day, and the last day is mopping up any topics we missed on the second day and talking about the product and sharing notes between each other where one site does something unique and helps another site out. I always enjoy myself during these events because it gives me a ringside seat to some of the biggest changes to the software, brought to us by our Sage rep, as well as some of the biggest challenges to how we use the software. I am a DBA / System Admin so I see things in terms of IT, hardware, training, and the logical parts of how things are arranged. I also have a ‘unique viewpoint’ which often times is at odds with the more soft-pedal approach that most people prefer. I’m brash, sometimes vulgar, but I bring the same passion to these discussions that I do to my workplace. I don’t do anyone any good if I just shuffle along and mumble as a yes man. Fortune favors the bold. If nothing else, I am bold. Sometimes I’m a few other four-letter things too, but bold is nice and friendly.

This year we visited NIU, and it’s the first time I’ve ever been to Dekalb, Illinois. On the way up my coworkers and I got to talking about Illinois trivia that doesn’t have anything to do with Chicago, which is the obvious 800 pound elephant taking a figurative dump in the corner. The only thing that I could readily volunteer was that Illinois is the nation’s number one producer of pumpkins. Funny what you get from a John Carpenter’s Halloween movie trivia track on the DVD. 🙂 I am the movie generation. We see things in terms of movies that we’ve seen, and in a lot of ways we relate to our world through the vocabulary of cinema that we are fond of. I have lost count of the number of times I’ve recognized situations that come out of Airplane!, Clue, or Princess Bride. To say nothing of the endless quotes from those movies. People often times wonder where I get my oddball humor from and I tell them time and time again that those three movies are a great place to start to get to know me better. My mind spends a lot of time thinking about those movies.

Northern Illinois is to Dekalb in a way that WMU is to Kalamazoo. Both schools sort of hug their cities and bring a certain flavor to the area that otherwise wouldn’t be there. Western has our Bronco and the colors from the black-eyed susans that grow here – while NIU are the Huskies and much in the way that Chicago pushed painted plaster cows as a cute city theme, Dekalb pushed painted plaster Husky dogs. Every mascot is adorable in their own way. Cows are harmless herbivores. Broncos are exceptionally handy to ride (and not eat, or turn into glue, Frau Blucher!) and Huskies are arguably one of the most recognizable and adorable dog breeds there are, plus they can pull a sled. The University staff welcomed us warmly and went above and beyond to ensure that our get together was a success.

During these meetings we cover a lot of topics and the overarching theme that I kept on noticing isn’t so much technical issues but rather strategy questions about prospect management. Of course listening to all these schools talking about how they manage their prospects gets me thinking about ways to once-and-for-all solve the issues they all have. Many of them bring it up over and over again and many institutions really kind of muddle along. I see a divide between those that understand the technology and those that have to use the technology but often times aren’t really keen on understanding what they are using coming into conflict with each other. It’s a lot like the gulf that develops between IT staff and those that they support. I’ve written about incuriosity before, that it leads to a kind of prized ignorance and ultimately devolves into an unpleasant puddle of rank dependency. Those that cannot depend on those that can to help them do their jobs. It’s a whirlpool sucking at the overall efficiencies of an organization as nobody can really be said to be nimble when they are trapped in this unusual back-and-forth between executives who should use the system but do not and the support staff that help them by, in some cases, only achieving progress by applying blunt force to the situation.

Much of my exasperation, because that’s really what it is. It’s not irritation although I’m often irritated, but mostly I’m just exasperated. I was raised with a certain work ethic by my parents that has driven me my entire life. Take pride in what you do, be responsible for your actions, and be motivated enough to get your work done in a timely fashion. While my closest coworkers were with me on this trip, I was a party to several conversations which I won’t really go too deeply in on my blog because the thoughts that I harbor in my mind aren’t the most complimentary or charitable when it comes to some of the workplace issues that surround me. The only thing I can really write about is how I feel and what I would do in the situation that has been described to me. It’s mostly a part of my upbringing. I chafe strongly whenever I am a witness to certain entitled vanities. It’s the reason why I’ll never be regarded as senior management because I refuse to sugar coat my opinions. I am not a yes man, I am passionate and outspoken and sometimes exceptionally blunt. I think what really bothers me deep down is laziness. I feel awkward and ashamed if I’m just sitting around spinning my wheels and doing nothing for anyone. I feel driven to help, address issues, or at least try to make a difference in a project or someone else’s life for the better. This drive of mine is in direct opposition to my perceptions of indolence that I see from time to time. I also prize my bluntness. If people can’t or won’t do what is expected of them, then perhaps they should seek out something else to do that suits them better. There is a name for the kind of manager I would be if I had any power, that would be “Hatchet Man” as I would more likely fire someone for being willfully indolent rather than have them hoovering up resources and masquerading as human anchors. Whenever I hear “nobody listens” my nearly reflexive response is “Make their employment hinge on it. They’ll listen then.” which goes over like a leaded balloon. I spend a lot of time keeping quiet under the banner of “If you can’t say something nice, don’t say anything at all.” Plus it’s a good thing I don’t have any power over anyone. I’d be a monster.

Another thing that pops up over and over again in my mind is the fable of “The Emperor Has No Clothes” and I find my mind dwelling on the story a lot. There isn’t anything that can be done because in some situations you simply have to endure the awkwardness of the situation. You should speak up, you should say something. You should grab the Emperor and shake him or her like a deranged british nanny and try to wake them up. But in the end you don’t. You just sit there, floating in an irritated miasma and over time it eventually wears down all your sharp edges into smooth dull rounded ones. I sometimes have little fantasies that I like to entertain from time to time, what I would do in certain situations. I end up imagining what I would do in other functional positions with what I know of my passion, my drive, and my work ethic. That I just can’t sit around waiting. That I’ve got to do something, anything, because not doing something would be hell. A good part of this all is that I’m an accomplishment junkie. I love the emotional rush of getting something finished. I don’t care about recognition, I’m quite happy being the ignored little cog in the great watchmakers design, but this cog will do something! I think that’s what I left with from NIU and this mini-conference. What would I do in some of these situations? I still have the idealism and energy of my youth and I’d make sweeping pronouncements and back it up with aggression nobody has ever witnessed. Again, it’s a good thing I keep my own counsel and stay silent.

Beyond some of this more aggressive stuff, we were able to help other people out with some issues they were having and sharing the plight of one institution almost always leads to other institutions either suggesting a new thing to try or accidentally fixing something for someone else who happens to be a semi-involved bystander. It also helps to know that you aren’t the only one, that other people are wrestling with the same kinds of things and in the way that you aren’t really alone offers a kind of consolation. You aren’t singularly damned, you’re just like all the others who are struggling with, well, whatever it may be.

At the end of our meeting we opened the floor to a general question about who might host next time. The fine folks from the University of Wisconsin Foundation leapt at the opportunity so next year we’ll be Milwaukee-bound. I am looking forward to it.

Filthy Predicates

A tale of woe, a tale of SQL woe. This is what the past two days have been all about. At work, we have our master database which contains all the demographic and biographic information that allows us to do our jobs. It tracks donors, constituents, alumni, individuals, technically everyone.

We have arranged with an external vendor for extended services, the vendor is called iModules and part of my job is to help ‘bridge the gap’ between these two systems, at least data-wise. So, the primary question is, how do we update data on iModules from Millennium, Millennium being our CRM database. With SQL of course! Silly! That was the albatross I spoke of on Twitter yesterday. Mostly the construction of update scripts isn’t terribly difficult only time consuming and involves a lot of typing and checking up column names, our addrline1 is their Address_1, that sort of thing.

Before I can send updates there are two attributes that need to be added to various records to mark them as “ripe to be sent to iModules” and that’s what has had me stuck for the past two days. Our system has two distinct and not clearly compatible ways of extracting data. There is Millennium Internet Reporter, called MIR, that the report writing people use to extract data from the database and then there is little old me with Aqua Data Studio. My report-writer coworker, Lisa, handed me a copy of the criteria that MIR uses to extract which people get these attributes added to their records. It’s a pretty straightforward presentation, this from that, never in that, with these values, so on and so forth. Almost always these queries start with a very simple SELECT block and then start growing from there. Almost always I end up using JOIN or LEFT OUTER JOIN in order to collect the right data. Turns out in this case, JOIN was exactly NOT what I needed to use. Lisa gave me a number, a magic goal number for the number of records that my query, if it’s correct, should pull. This number is 687. When I started I got 287433. Then I adjusted the query and went to 133496. Over time I bounded all over, from 115 million all the way down to 20. Never really hitting that magic number. There are a lot of little gotchas that you have to be aware of and code properly. The sense that the query depends on is that we want to select certain people with certain values OR people without any values at all, but not a specific kind of value after that. I was wandering around trying various methods of attack, pulling the criteria out into temporary tables was one, switching all my joins to left outer joins (that lead to 115 million, oops) and then I thought I had it and was really clever when I enriched my joins with subqueries that used the IN predicate. Even then, I couldn’t get below 22500 records pulled. Remember the goal number is 687. There were some more little gotchas, for example, I forgot to remove the dead from the list, so that got me down to about 2700. Then I started to read about some of these predicates and I had a passing familiarity with SQL’s three-value logic property. In SQL there is true, there is false, and there is UNKNOWN. 1=1 is true, 1=0 is false, 1=null is unknown and null=null is unknown. Turns out my problem was all wadded up with the IN predicate that I had used. IN was inappropriate for this use, as it utterly ignored all the ‘null’ cases, the ones I wanted to be selected. Turns out there is a predicate I have never used, called EXISTS. This predicate changes the sense of what is selected and when I reorganized my query to use EXISTS I went from 2700 to 1686. But still, 1686 is greater than my magic goal of 687, so there was something else I wasn’t seeing. I had removed the dead, the logic looked spot on – as I read from the criteria page that Lisa had given me it read spot-for-spot “bang on” correct. Every time I ran the query it dutifully spit out 1686 records. So, what the hell was I missing?

Computers do exactly what you tell them to do, nothing more and nothing less, unless you find a bug. There aren’t any bugs in my way so it was a failure with my query, somewhere. I listed out all the selected records and started to look at them in the database, seeing if I could spot something in the selected group that shouldn’t be there and that I missed in my query logic. The first record I brought up was utterly incorrect, as the “title bar” had the word “Duplicate” in it, and my query clearly states “NOT LIKE %dup%” so why the hell was it still selecting records with Duplicate in the title bar? Yeah, case. That’s what screwed me. Case. SQL Server is very dutiful and stripped out all the places where the LIKE clause found the text fragment of dup. But not Dup, or dUp, or duP. Or agonizingly, Duplicate. Because a scan for ‘dup’ will never be true when given ‘Duplicate’ to look at d <> D. So once I wrapped the title bar column name in the lower() function, and re-ran the data query, SQL dutifully spit out 687 records. My magic number.

So I won, god damned it. It took switching from IN to EXISTS, pitching JOIN overboard, taking out the dead people and forcing lower-case reckoning. So now the damned thing is done and I can move on with my life!

Law of Unintended Side Effects

Yesterday, around noon I took our databases IIS component offline and disabled the database triggers on the “attribute” table. I then inserted 248k attributes into the database, took all of about 5 minutes. I re-enabled the triggers and everything was working well. I went home, without a care in the world.

Last night, at around 3am my nightly-scheduled “Data Sync” program ran on my primary database and it’s designed to markup “attributes” that are missing some key details. This script ran into my 248k attributes and was busy processing them, in fact, it was busy doing that all along until I discovered it was still struggling at Noon today. Of course I sort of knew something wasn’t right as I didn’t get a successfully completed email from my database server telling me that this program had completed. I should have had the presence of mind to check first thing this morning, but, like many things, it slipped my mind. 🙁

After I discovered what was going on I fiddled with the triggers and mopped up the data. Now all is right with the world once again. To say I feel foolish is an understatement. I really have to work on my spidey-sense when it comes to these things. 🙂

“All’s Well that Ends Well”…