Automating boring ticket creation

Working as a technical support assistant required a lot of patience but overall it’s too easy. I guess that’s why Connecticut Distance Learning Consortium had us TSAs also learn the financial aid support responsibilities. Besides taking inbound calls and emails, creating support tickets, learning the financial aid software Jenzabar and Banner, and troubleshooting technical issues for students across the state (some across the country), we also were instructed to make outbound calls.

An excel spreadsheet with hundreds or thousands of lines would be given to a team of TSAs and financial aid assistants to manually create entries in our ticketing system Kayako for each and every row. I asked one of my managers if they had any software to automate the process, but he told me that they (Kayako) charges more for that feature. I was stunned that people had been wasting their time on a simple task which can be automated easily.

First solution:

The very first code I wrote to begin the automation was a Windows form in C#. The implementation is primitive at best, using web browser controls to log in and create tickets, timers to control the browsers, and a comma separated values file parser. Excel has a feature to export to CSV so this made reading input trivial.

while((line=sr.ReadLine())!=null) // parse input csv by row
{
string[] s = line.Split(‘,’); // s is the array of columns
...

The web browsers were a poor decision insofar that they were slow, unresponsive at times, and around every 300–400 tickets they would leak so much memory that the app would crash. I found some workaround to manually call the garbage collector after each ticket but I was still unsatisfied with the speed.

void b_FormClosing(object sender, FormClosingEventArgs e)
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
ticketsCreated+=1;

beginNewTicket();
}

My manager would ask me to bring in my laptop to work; he would send me an email with some excel files attached and I would run them through the application.

Second solution:

Exploring different approaches I found that a Chrome extension might work out well. I couldn’t use anything like Python or C because I was working on state computers and installing anything would require permission I didn’t want to ask for. Chrome extensions can be developed on any computer that runs Chrome and any text editor.

Running/debugging the extension produces no executable files so I didn’t have to worry about talking to the IT department (although my managers were informed).

The extension works simply by taking 2 inputs, a unique POST parameter found through reverse engineering the ticket creation web request and a CSV file. The template POST form is loaded from a text file and the user manually changes/adds values as necessary. The button does the rest of the work, such as parsing input and sending requests.

Kayako implements a cross site request forgery token so the extension parses it out and appends it’s value to the POST request. This function also places the column information from the CSV file into the appropriate spot in the request (designated %columnNumber% in the template).

function buildPayload(tickets, boundry, csrf, csvLine)
{
var ret = tickets.replace(/%boundry%/g, boundry);
ret+=’\n — — — WebKitFormBoundary’ + boundry + ‘\n’+ ‘Content-Disposition: form-data; name=”csrfhash”\n’ +
csrf + ‘\n’ + ‘ — — — WebKitFormBoundary’ + boundry + ‘ — ‘;
var retParsed = ret;
var i;
for(i=0;i<csvLine.split(“,”).length;i++)
{
var re = new RegExp(“%”+i.toString()+”%”);
retParsed = retParsed.replace(re, csvLine.split(“,”)[i]);
}
return retParsed;
}
function getCSRFhash(theUrl)
{
var html = httpGet(theUrl);
var token = html.split(‘csrfhash” value=”’)[1].split(‘“‘)[0];
return token;
}
function uploadTickets()
{
var i;
var ticketInfo = document.getElementById(“tickets”);
var ticketInputInfo = document.getElementById(“csv”);
var csvi = ticketInputInfo.value;
var ticketi = ticketInfo.value;
for(i=0;i<csvi.split(‘\n’).length;i++)
{
var csfr = getCSRFhash(“http://esupport.ctdlc.org/staff/index.php?/Tickets/Ticket/NewTicketForm");
var http = new XMLHttpRequest();
var url = “http://esupport.ctdlc.org/staff/index.php?/Tickets/Ticket/NewTicketSubmit/user";
var rnd = randomString(16);
var params = buildPayload(ticketi, rnd, csfr, csvi.split(‘\n’)[i]);
http.open(“POST”, url, false);
http.setRequestHeader(“Content-type”, “multipart/form-data; boundary= — — WebKitFormBoundary”+rnd);
http.send(params);
alert(http.responseText);
}
alert(“Done”);
}

These applications were used to create well over 30,000 outbound tickets for colleges across the state of Connecticut.