Ticket analysis with reflection

Ticket analysis with reflection

I wrote a bunch of tools to analyze the ticketing system back when I worked support. Most of this was to prove to myself that I wasn't going crazy and that two or three people were doing most of the work in the company. The tools did their job and showed me that my suspicions were correct.

One of them was called "reflection". It was designed to take a starting date and time and an interval. If you wanted to see what second shift did, you might say "start on December 17th at 4 PM and run for 9 hours". It would then look at the time from 4 PM to 1 AM the next morning.

I had it pull every single action to the ticketing system I could find for that span of time. Then I kept track of who did each action and then added it all up. Finally, I sorted by name and built a table. It looked a little like this.

Tech Comments added Work logged Status changes Tickets created Tickets touched Monkey score Tech #1 38 13 57 13 18 139 Tech #2 64 32 107 4 33 240 Tech #3 12 8 27 4 4 55

Obviously we had way more than three people using the system in a given nine hour period, but this is just an example. You might have noticed the final column, which is something I called the "monkey score".

The "monkey score" is just the sum of the other columns for that particular individual. It gives you some idea of how much clicking around that person did. I figure that's something a trained monkey could do, hence the name.

You might also notice that some of the cells in that table have a green background. This is not a coincidence. Those are the highest value for that particular column. As you can see, Tech #2 here "won" everything but the count of tickets being created.

There is a simple explanation for this: Tech #1 was just a phone firewall. He'd answer a call, get some data from a customer, open a ticket, and then get them off the phone. That plus his other responsibilities of turning monitoring alerts into support tickets meant he would create more than a higher-level tech.

This one was pretty good, but then I wanted more. I wound up creating a variant called STR: Single Tech Reflection. It followed the same idea of tracking all of the actions taken within the ticketing system, but it only did it for one person at a time. It sorted everything by time. It looked a little like this.

13:01:45 Login to ticketing system: 192.168.34.11 13:02:12 Assignment: ticket #1234 (bats in the belfry) to self 13:05:35 Public comment: ticket #1234
Hello customer_name,

The bats are supposed to be there. We harness their wing power to run your server. Thank you for choosing hosting_company.

Tech Name

13:05:36 Work logged: 2 minutes 13:05:36 Status change: ticket #1234: supposedly done

IDLE TIME: 2 hours 15 minutes 2 seconds 15:20:38 Status change: ticket #3456: solved

This one allowed you to see a tech's entire day without having to jump around from ticket to ticket. If they did something, you saw it. Even if they never had a ticket assigned to them, if they modified it somehow, it showed up here.

Originally, I was using this to show just how idiotic the lives of some people had been. There were so-called "level 3 techs" who were spending their entire days just passing tickets around and repeating the things other people had said. They always avoided the tickets which involved actual work.

This tool exposed all of that. You could run it against one of these slackers and then against someone who still had a soul and it would paint two very different pictures.

In the second release, I added the big red IDLE TIME block for any span of time more than 1 hour but less than 12. This way, if someone disappeared for a long period of time, it would become obvious. The reason it has the "less than 12" provision is because third shifters would have a huge gap in the middle of the day otherwise.

Once this tool became popular, people wanted to sic it on a bunch of other techs. This was all well and good, but there was the problem of trying to figure out what day a given tech worked. A bunch of people didn't necessarily work Monday through Friday, and it was annoying to just keep plugging in dates until something worked.

As my final part of this puzzle, I built a calendar. It was simple enough: you give it a tech and a month and it shows you the days they logged into the ticketing system. The theory is that you have to log in to actually perform anything, so a day with a login is bound to be interesting. You could just click on that day to see the results in a table like the one above.

STR calendar for Tech #2
December 2011

Sun Mon Tue Wed Thu Fri Sat

1 2 3

*** *** *** 4 5 6 7 8 9 10 *** ***

*** *** *** 11 12 13 14 15 16 17 *** ***

*** *** *** 18 19 20 21 22 23 24 ***

25 26 27 28 29 30 31

What you see here is that Tech #2 obviously works Thursday through Monday every week, and that there's no data past December 18th (since that's today).

I'd like to say that all of these tools brought about a new era of enlightenment where the bosses went and culled a bunch of dead wood. Then again, I'd also like to say that my pet unicorn brings me the newspaper every morning before driving me to work.

Oh well, I can dream.