Building a Ticketing System Using osTicket / Ubuntu / Apache / MySQL / PHP

Note: a "ticketing system" allows a support team to keep track of their open cases. It facilitates organization and significantly improves productivity because it consolidates information about support cases into a centralized location and streamlines communication about the cases.

Ever since I joined the Middle Georgia Regional Library system, I wanted to create a new ticketing system for staff to use. Google Forms (as a ticketing system) was lacking a lot of useful features that would allow us to do our jobs more efficiently. When using Forms, help desk agents are not able to directly respond to tickets, correspond with other agents about tickets, receive notifications, produce statistics, mark tickets resolved... and so on. The end-user is not allowed to check the status of their ticket or add an attachment without contacting one of us directly. Also, I noticed that my department was spending a considerable amount of time communicating information about the tickets before we had even begun the process of solving them. Questions such as: Have you seen that ticket from John yet?, Did you ever get a response?, and Can you forward that attachment to me? were repeated enough times throughout the week that I knew a better, more centralized ticketing solution had to be out there for us.

I started to research our options and found many cloud providers could get us going in no time, but I was hesitant to pursue any of those because they were not free. It would not fare well for my pitch if I was adding a new recurring cost to our department when Google Forms is free and already gets the job done, even if it isn't built to be a ticketing system. Coincidentally, around that same time, my boss told me that there was a server machine (a Dell PowerEdge T110 II) from the past administration that she had no use for, and I could tinker with it if I wanted to. Because I wanted a free option and had a server machine available, I settled on the fact that I was going to host my own ticketing system at the library using this machine.

After coming to this realization, my research became very easy because I knew exactly what I wanted: something free and open-source, capable being hosted locally, and packed with all the features missing from Google Forms. Welcome: osTicket. osTicket is not only free and self-hostable, it provides support agents with a centralized communication platform and enables end-users to input tickets and check on the status of tickets via the web, integrates with email, allows attachments, and many other features that "just make sense" for a ticketing system to have.

I decided to install Ubuntu Server on the machine to host it because it is also free, open-source, lightweight, has a huge community to provide support, and within my realm of experience. I could have used the Windows Server 2016 partition on the machine, but I felt that using IIS would be overkill in this case.

implementation The stack is defined as follows:

Program Name	Purpose
osTicket	Provides all of the web pages, functions, and setup; it is the backbone of the ticketing system.
Ubuntu Linux 20.04	The OS that houses everything.
Apache2	Makes the server accessible to the open Internet, broadcasts on ports 80 (HTTP) & 443 (HTTPS)
MySQL Server	Acts as a database engine. All information such as tickets, users, configurations are stored in the SQL database. This is how the webserver has access to all the information it wants to serve.
PHP 7.2	Common server-side scripting language that enables the web server to communicate with the SQL server on the same machine.
Certbot	A command line program that enables the automatic renewal of SSL certificates (required for HTTPS)

I began by installing a fresh copy of Ubuntu Server and then installing & configuring Apache2. This process took a considerable amount of time because I had never used Apache to host websites in the past, so it took me some time to figure out the syntax of the config files. But, after Apache was configured correctly I could access the webserver from the machine’s browser by typing localhost in my browser's URL bar. To ensure that the webserver was accessible from outside the current network, I placed a ticket with Windstream to open ports 80 (HTTP) and 443 (HTTPS) on the library's router. While waiting on that to be resolved, I installed and configured a local MySQL Server and installed PHP. osTicket took me through the rest of the setup process of installing dependencies and tying everything together. The web server was up and running with a fresh copy of osTicket! Now to configure the server. Customizing each page, adding help topics and departments, adding email configuration, tweaking other settings did take some time. You may find more info about this process in the osTicket documentation.

I then gave the public IP of the server machine to The University System of Georgia (who manages our bibblib.org domain) so they could set up an A record for a new subdomain using this IP address. The purpose of this was to ensure that the server could be accessed from the Internet using the URL help.bibblib.org.

So, the webserver is up, it’s accessible from the Internet using a public domain. Great! About half of the project is complete at this point. I then trained my supervisor on how to manage, maintain, conduct backups, and any other pertinent information about the server. Also, this is a machine that is publicly accessible from the Internet so protecting it (and the rest of our network) was a must. I followed an Apache server hardening guide, installed Fail2ban, and made sure HTTPS was strictly enforced.

Finally, I needed to migrate everyone over to the new system. I was able to export staff names and emails to a CSV file from my Google Admin dashboard. osTicket conveniently supports CSV import, so all the staff was moved over in a matter of minutes. Everyone logs in with their work email and sets their own password. Of course, these passwords are hashed and not visible to anyone, including myself.

Conclusion The project as a whole took a few months to complete. After all, it was never formally assigned to me. It was a product of my initiative that I would work on when I had a break from my usual duties. It was incredibly worth the wait, however. I was able to provide a tremendous service to MGRL who will benefit from this ticketing system for years to come. I can also say that I personally benefitted from this project because I received so much learning and practice about serving webpages using Apache, SSL certificate authorities, server hardening, MySQL databases, and Linux.

I have since received so many compliments from staff who find the new ticketing system easier and all-around better to use. It has provided my department with the centralized communication we needed on top of many quality-of-life improvements that we were not getting from Google Forms. It has been working correctly with no intervention for over one hundred days now, so I would consider this project a great success!