Measurements are great. It’s how you identify what is what. How you figure out how fast you are going in a car, how soon you will get to your destination, how well something is working…. it’s everywhere. The real question is, how you use the information to get what you need.
That was my challenge this week. I had a database chock full of information from our helpdesk system. But, I needed to build reports that was meaningful to answer the future. Oh, and did I mention I had a deadline?
The problem: The Network Operations Team (desktop, server, communications, etc) is consistently not able to keep up on demands. Identify the demands, the why’s, and determine a fix.
So, the first thing I need to quantify, was how do we get so far behind in ticket closures in the first place. When does it happen, and find a pattern. By taking the net result of open to closed tickets per week, I created a “Gap” chart. The “Gap” is the difference of open to close. If the net result is positive, we opened more tickets than were closed adding to the backlog. If the net result is negative, we were decreasing the backlog:
Okay, so now I know when and by how much we were either increasing or decreasing the backlog. I can match these weeks, with what was going on with the business at that time. If these events are recurring, say, when Microsoft releases their updates (but *THAT* never happens, right?), or something else, we can plan for it.
Now, the question is…… just what exactly do you plan for? How do you know what you can and cannot take care of? Well, there is never going to be an exact answer, but thanks to some clever statisical analysis tool called “Average”, we can do an educated guess.
Since we know how many tickets we closed per week, for the last 52 weeks, we simply add those tickets up, and divide by the count. Now, you know how much your team closes on average per week. Divide THAT number by the days in the week, and you can figure out your closures per day. You can even go one step further and divide again, by the number of people in the team you are focusing on. Now, you have average closures per person, per day. And because we are spanning 365 days, the result is just about as good as it gets.
So, now that you know how many tickets gets closed per week, and you have a general idea on how many tickets get opened per week, you can plan ahead and make sure you have enough “resources” to get the job done. Chances are, you’ll need less resources than the numbers give, because you are armed with one critical tool: KNOWLEDGE. You know what to prepare for, and you won’t be surprised again.
Taking the proactive approach ensures that we are not only forward thinking, but we decrease possible downtime, because we learned from our own mistakes.
I know I did……………
So, maybe you are asking yourself, “Dude, that sounds pretty obvious, why so stressed?”. Well, it’s not like our helpdesk application (which is Servicedesk by ManageEngine), has a built-in “Gap” report. I had to build my own SQL Query and build the chart in Excel. Lots of inner joins and WHERE clauses. Add to that, the application stores time in Unix Time, which is the number of milliseconds since Epoch (not to be confused with Tupac), which is since January 1, 1970.
I also built other reports in SQL, such as a cumulative open report by week, and a week-by-week status of open and closed line graphs too. But, the “Gap” chart, I think, is the most explanative.