Data entry scripting - an interesting challenge

The company I work for recently a week-long outage of a ticket system that resulted in a lot of wasted time and energy to recover the backlog of tickets. I have been tasked with implementing a system to allow the backlog to be dealt with more easily than our technicians writing their tickets in notepad during an outage and then copying them over when the system comes back up.

At a minimum I am expected to implement a means of creating temporary tickets and saving them until the system comes back online. Having a temporary ticket system with all of the same fields and information as the normal system would allow any user to input any other user’s tickets, speeding up the process. That in itself isn’t a huge challenge, it could be accomplished with Microsoft Access in a few hours. However, my ultimate goal at this point is to automate the input process once the system comes back up I’d like the user interaction to be as minimal as possible.

My first ideas concerning this were command line prompts into the ticket system. Unfortunately, due to the nature of our contract, I can’t get or give users access to the back end of the ticket system, nor can any other software or drivers onto machines; so my options are vastly limited as far as how I can approach this. Being unable to access the back-end of the ticket system, the only thing that immediately springs to mind is to setup a basic macro to run which copies and pastes information from the script GUI into the ticket system. I’m just having trouble finding any means of doing this. PowerShell seems to have the most promise for developing this at the moment, but was hoping there may be some guidance more experienced users can offer concerning this.

I appreciate your time.

Few questions:

  • What’s your ticketing system? Is it something like Remedy, or Service Manager, or . . . and what version?
    I need to know that to identify how and where data is stored. For example, Remedy and SM use SQL, while osTicket uses mySQL and Apache…
    Can you identify the version of the database used, and whether it’s clustered or not, and other database configuration that may be useful here like alwayson . . .
  • Identify where the temp data is. I understand it’s in text files. Identify further details like: one text file per ticket, and data structure within the text file . .
  • Identify a task objective. This may sound trivial but a clear definition is essential to guide the action plan and will dictate technical options
    For example, if the task objective is to “build a temp ticketing system as an option for end users to use when the primary system is down” defines one task, while “import last week’s data from end users’ text files into the current ticketing system” defines a completely different task.
    If the task is the latter, the biggest challenge in my opinion is lack of source data conformity. Data that users have entered in text files will not have specific fields in specific locations. So, a script to bring that data into a database will have the difficulty of identifying what data from a given text file to bring into the ticket number field for example, or what data to bring in as the ticket description, or status, or received, or . . .

I’m personally a staunch opponent of patching things up, or putting a bandaid over a bandaid until the system falls apart. I also don’t like reinventing the wheel and building a ticketing system from scratch while there are plenty good ones out there.

If I were in your shoes, I will determine whether the outage is primarily due to the ticketing system software not able to handle the data size or concurrent users load for example, or a hardware failure due to poorly designed or implemented infrastructure.
If it’s a software issue, I recommend Microsoft Service Manager which is part of Microsoft System Center. If you’re an enterprise Microsoft client, you already have it. There are many ways to import data to it from other systems, and it fully supports powershell
If it’s a hardware issue, that should be easy to fix. In this day and age we can and do make every single element redundant in a typical enterprise data center. I’ll be happy to give you quick high-level design direction if you wish to share your setup details.

I agree with Sam, why would you develop a temporary ticket system versus just making the other highly available through clustering, etc. It’s not a question of can it be done, it’s why?