The Help Desk Management System
Mark Mellman I needed to find a way to manage users' requests more efficiently because they exceeded my ability to remember each of the issues. We hired additional people in the help desk area, but the problem grew exponentially. The users complained that they were not kept informed on the status of their problems (usually because we forgot what their problem was). I needed a solution quickly, as the problem was becoming worse because of a rapid increase of users. I developed a help desk system using the Web, which allowed users to request services and track the progress of their requests. The back end consisted of CGI scripts using Sybperl, which gave the help desk personnel both a list of "issues/things to do" and the capability to assign tasks to different support people. Assigning problems to specific help desk support staff and allowing users to check on the status of their request, or ticket, greatly improved the communication with the users (See Figure 1.). Because the information is kept in a relational database, I can now get answers to questions such as: "How many open (assigned) requests exist, and what is the average number of days open for those requests per support person?," "Can I get a list of all open issues, in chronological order, with links to get a detailed status report of each issue?," "What have I been doing for the past month?," "What has everyone else in support done for the past month?," "Which user/department asks for the most support?," "Is there a pattern to our support?," and "Could we solve some of these problems with some training?." This help desk system will work in its present state for anyone who would like to use it; however, each organization's needs are different, and changes will be required in order to maximize this system's effectiveness. The software can serve as a working example of how the Web can be used to access and modify relational databases for other purposes. Details I originally put up a Web page from which the user could fill out a help desk request, or ticket, which was then mailed to the support department group. Members of the support department would then discuss who would take the ticket. It seemed more useful to keep all of the trouble tickets in a relational database, and because we are a Sybase shop, the choice was easy. For rapid development, I decided to use Sybperl for the database access. The scripts listed here are written to use Sybperl release 1.011, which is Perl version 4.036 with Sybase dblib routines. Sybperl version 2.5, which is Perl 5 with ctlib routines, is the newest release and should be backward compatible, although I have not tested it. We implemented the help desk system at our company as follows. On our main Intranet home page, we added a link for "Computer Help Desk" (hdindex.pl). This page has four options. The first is "Create/Submit Helpdesk Trouble Ticket" (helpdesk.pl) - if this link is selected, the user is presented with a form to fill out in which he describes the help needed. Information is stored in a database, and email is then sent to the help desk group and the user (helpdesk1.pl). The second option is "Ticket lookup by name (open or closed)" (hdname.pl). This option allows a user to look up all tickets that she has opened or closed. Once the user is presented with a list of tickets, she has the option of looking at the details of that ticket by clicking on the ticket number, which brings up all of the information about the work performed on the ticket. The third option is "Find status on specific Ticket" (hdstatusHTML.pl). This link is used to look up the status of a specific ticket by its assigned number. It displays the same information as the above option, but you must supply the ticket number; it is a more direct way of getting the status of a ticket, if you know the number. The fourth and final option on the "Computer Help Desk" page is "Systems Support Page" (hdsupportHTML.pl), which requires a password to gain access - the default password upon installation is "Password." Once the correct password is entered, a page is displayed with the necessary links for the support people to modify the tickets. There are two options on this page. The first option is "Ticket Maintenance" (hdassign.pl), which is used to assign tickets to help desk support people. Each time a ticket is modified by the support person, an update via email is sent to the submitter. There are also fields to keep track of time spent on each item, so a total time spent on each ticket can be calculated. The second option is "Ticket Maintenance Options" (hdsupport2.pl), a link to another page with links to four reports. The first report "Open Ticket Report" (hdopen.pl) allows a support person to display assigned tickets sorted by priority and date. This report is used as a "things to do list." The second report, "Days Open Report" (daysopen.pl), lists all open tickets chronologically. The third, "Tickets by Owner Report" (hdowned.pl), displays a table containing the number of open tickets for each support person and the average number of days the tickets have been open. The fourth report, "Tickets Opened per Week Report" (avgticket.pl), displays a table with the total number of tickets opened and average tickets per week (calculated on a 5-day work week) for the past 10 weeks. There are numerous other reports with useful information that could be written. One of the reports I am currently working on displays the average amount of time taken to close a trouble ticket by a support person. Please refer to Figure 2 for a list of the scripts and their functions. Security Concerns I have opted to restrict the operation of this system to our Intranet at our site, because I do not feel comfortable running CGI scripts on a firewall machine that accesses a production database server. Please review CERT advisories CA96.06.cgi_example_code and CA-96.06.README for further information on cgi-bin vulnerabilities. Installation To install the help desk software, get the tar file (available from ftp.mfi.com in /pub/sysadmin) and extract it. Move all .pl and .inc files to your cgi directory. You must have Sybperl installed and working - you can download Sybperl from:
ftp://ftp.demon.co.uk/pub/perl/db/perl4
You must create a Sybase database and a new user login that will require SA privileges. Once the database is created, you must create the database schema. This is done by executing the following command:
isql -U<username> -P<password> -ihelpdesk.ddl
<username> and <password> are those that you have created above. The file helpdesk.ddl contains all of the "create table" commands. If your database name is different, you must change the first line in the helpdesk.ddl file to the name of your database. See Figure 3 and Figure 4. The file helpdesk.inc should be located in your cgi directory. This file is included in all of the Sybperl scripts, and contains information such as: other includes required for Sybperl to work, login information for your databases, and global variables. You should modify this file to use your site-specific information. See Figure 5 for a list of environment variables with their descriptions. You may think that part of the schema seems inappropriately complex for the tasks that I am accomplishing. For example, I use an association table to get department names for each employee. I did that because my site already has a Human Resources database that I access. I have included the tables and entities required to get the help desk software running; however, it might be more useful to see if your site has a similar database, and make the changes for the software to use them. Although there are likely more elegant ways to accomplish many of the tasks within this package, we have found it to be reasonably functional. The system, created with tools and expertise at hand, solved the immediate problem we faced and has helped us handle user requests more efficiently.
About the author
Mark Mellman is Senior Manager of Networks and Systems at Millennium Pharmaceuticals in Cambridge, MA. Mark has 12 years of experience in the UNIX environment at companies such as EDS, National Medical Care, and Budget Rent-a-Car. He has developed applications in diverse areas including bioinformatics, financial, network automation, physician management, database, and Web pages/cgi scripts. His current interests include network security, Java programming, network topologies, large UNIX servers, and database design/administration. He can be reahed at: mellman@mpi.com.
|