Vedder Price P.C. Help Desk Database Documentation

Overview

The Vedder Price P.C. Help Desk Database is a web-based application for managing tickets, users, and hardware. It is divided into different areas of general functionality, which are accessible via the main navigation tabs. Below the tabs is a subnav bar that contains links to more specific functions. Clicking a tab or subnav link takes you to a page that displays information in the database, allows you to add or update information, or both.

Some pages may perform more than one action, depending on whether any parameters are passed along in the URL. For example, any ticket in the database can be viewed by sending the ticket.php page a parameter ticket with the ticket number as its value. E.g., ADDRESS/ticket.php?ticket=4. [With mod_rewrite enabled and the proper .htaccess file, this can be written ADDRESS/ticket/4.]

Each ticket, therefore, has a unique URL and can be referenced as a link in an email or instant message.

The application and this documentation use the term "user" to refer to the end user who is being supported the term "staff member" to refer to the person who uses the database in a support role.

Tickets

The primary purpose of the database is to manage tickets. Within the Tickets area, a staff member can:

• create a new ticket
• view the open tickets assigned or escalated to him/her
• view all of the open tickets in the database
• view the tickets opened today (regardless of open/closed status), and
• view all of the tickets closed today

By default, clicking the Tickets tab will display the open tickets assigned or escalated to the currently logged-in staff member. The open tickets assigned or escalated to any staff member can be displayed by sending the index.php page a parameter staff with the staff member's username as the value. E.g., ADDRESS/index.php?staff=o_baty.

Create a Ticket / Edit a Ticket

The User field autocompletes as you type. It will narrow the list of users based on whether the letters appear in either the user's last name or first name, and sort the list alphabetically by last name. All of the autocomplete fields in the database will return a result on a partial-word match. For the User field, this means you can look up a user by first name or even part of a name. On the Create a Ticket page, the cursor is automatically placed in the User field when the page loads.

With all autocomplete fields, hitting the Tab key will fill the field with the highlighted selection. Hitting Tab again will move the cursor to the next field. Options in the menu can be selected using the up/down arrows on the keyboard or with the mouse.

The Subject field autocompletes as you type. Changing the value in this field will clear the value in the Topic field, if one exists.

The Topic field autocompletes as you type.

You can leave comments for a ticket much as you would leave comments on a blog post. To keep things simple, the Comment field is still part of the Ticket form and is saved using the Save Ticket button. The comment is timestamped when you save the ticket and added to the bottom of the ticket, with the most recent comment at the top. Possible usage: You change the ticket to be Escalated to an administrator, and leave a comment explaining why.

The Due Date/Time field uses the powerful PHP function strtotime() to convert a string containing a US English date format into a Unix timestamp. This means that you don't necessarily need to format the time as "MM/DD/YYYY HH:MM AM/PM". The string can be formatted using regular language, so "tomorrow 8:00 am", "tuesday 1:00 pm" (meaning the following Tuesday), and "july 23 4:00 pm" are all completely legitimate ways to fill in the field (just avoid using "at" between the date and time). When the ticket is saved, the Due Date/Time field value is converted into a Unix timestamp and that timestamp is put into the database. When the ticket is opened or displayed in a list, the timestamp is converted back into the "MM/DD/YYYY HH:MM AM/PM" format (or another format, such as the hours remaining countdown).

Files can be uploaded and attached to tickets. Only certain file types are allowed to be uploaded, for security reasons.

Once created, a ticket cannot be deleted.

Ticket Lists

There are a number of pages that display multiple tickets as the result of a database query. Multiple tickets are presented in tabular form, with each ticket occupying a row. The columns will vary depending on which page is displaying the tickets (i.e., a list of open tickets generally will not display a Closed Date column).

The contents of a tickets table can be sorted by clicking on a column heading. The table will be sorted based on the data in the column (alphabetical, numerical, by date, etc.). Clicking the heading again will sort in the opposite direction.

The logged-in Analyst's open tickets list refreshes automatically every 60 seconds.

One can do some pretty neat things by passing parameters in the URL to the list-tickets.php page. For example, to see all the Open tickets, pass the parameter/value pair: liststatus=open. To see all tickets opened today, pass liststatus=all&since=today. To see all the tickets closed in the last month, pass liststatus=all&since=last+month. As with the Search Results page, bookmarking the page will allow a user to search the database each time the page is visited.

Search

Within the Search area, a staff member can search the database for tickets matching any combination of criteria. In the case of a full-text search, the results will display a snippet of the text with the search phrase displayed in bold.

A search can use a single datetime or a datetime range. If only a start datetime is specified, the search will return any tickets created/modified/closed/due within the 24 hours following that datetime. If start and end datetimes are specified, the search will return any tickets created/modified/closed/due between the start time and 24 hours after the end time. If only an end datetime is specified, the search will return any tickets created/modified/closed/due before 24 hours after the end time.

A search can be saved by bookmarking the results page in your browser. The search will be performed each time the page is refreshed.

Note that if the search you wish to save involves a date/time parameter, you will want to enter "today", "yesterday", "last month", etc. as the parameter, rather than actual dates, so that the script looks for "yesterday" no matter what day it is.

When not using a Boolean full-text search, the search results for open tickets will be sorted by the ticket’s open date, in descending order. Search results for closed tickets will be sorted by closed date, in descending order. (This could be changed to use the last modified date or any other criteria.)

Reports

Reports are pre-configured database queries that have been styled to better present the anticipated result.

The Time Sensitive Tickets report presents the upcoming events in a graphical day-at-a-time view.

Users

Within the Users area, a staff member can look up and edit a user's Help Desk profile, look up and edit the hardware assigned to a user, and reassign hardware to a different user.

Each piece of hardware has a unique PCID, which can be added to the database in either the Users or Administration areas. A single PCID cannot be assigned to more than one user at a time. Assigning a PCID that is already in use will re-assign that PCID to the new user and remove it from the prior assignee.

PCIDs can be searched in two ways. The prefered method is to use the autocomplete search field to locate a single piece of equipment, but it is also possible to return a list of all matching pieces using the partial match field. A possible use of the partial match field would be getting a list of one type of hardware (all loaner laptops, for example), should they all have a common element in the PCID.

Alerts

The Alerts function is currently under development. Alerts help you monitor spikes in calls for a specific issue.

Here is how Alerts might work: If the number of opened tickets in the last "X" number of days for a particular Subject (or Subject+Topic combination) exceeds a set threshold, the tickets are listed here.

For example, if the threshold for a "Network Login" Alert is 5 tickets in a rolling 5-day period, once that threshold is exceeded, all Network Login tickets for that period would be listed on this page.

Statistics

The Statistics area displays statistical information about the tickets in the database.

Some possible statistics are: number of total tickets in the database, open tickets, open escalated tickets, tickets opened in the last 30 days, tickets closed in the last 30 days, each staff member's open tickets, tickets closed by each staff member in the last 365 days, and each staff member's average number of tickets closed per week.

Adminstration

The Administration area of the application can be restricted to staff with a "superuser" role.

Within the Administration area, a user can add users, change passwords, add PCIDs, and add and edit staff members. Due to the structure of the database, users and staff cannot be deleted. Instead, a user or staff member is categorized as either "active" or "inactive".

[Future feature: The superuser can also add subjects and topics.]

Technical Notes

This Technical Notes section of the documentation deals with the code behind the application. The application is written in the procedural style. It is comprised primarily of PHP pages and more modular 'include' files, located in the /includes/ folder.

Date and time handling

In PHP, dates and times can be handled in many different ways. The Help Desk Database saves dates as a Unix timestamp. A Unix timestamp is the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT). The Unix timestamp "1216949160" converts to 7/24/08 8:26 PM CDT. Using a real number to represent time makes it very easy to work with mathmatically. However, when converting the Unix timestamp to a MM/DD/YYYY type format using date(), the server's timezone will affect the output. Adjusting for various timezones is not easily done, and further complicated by DST. You can read more about the PHP function time() at http://us2.php.net/time.

The Help Desk Database relies exclusively on the server's system clock for generating timestamps, as JavaScript timestamps, which rely on the client machine's clock, are wholly unreliable (and also subject to timezone complications).

Database integrity

Once created, a ticket cannot be deleted. Comments are held in a separate table and linked to the parent ticket using the ticket's ID. If a ticket were to be deleted after a comment was posted to it, and that ticket's ID number was later reused, the comment would then be attached to the new ticket.

Autocomplete

The autocomplete functionality is implemented by the excellent Scriptaculous Ajax.Autocompleter (http://github.com/madrobby/scriptaculous/wikis/ajax-autocompleter). The point is to have a script query the database for a limited number of options, rather than load the entire list of possible options with every page. The Ajax.Autocompleter can populate a second, hidden input with a different value than the one displayed in the primary input field. This is essential, as the user's name is what we want to search for, but it's the GUID associated with that user that is attached to the ticket (the user's name is never put into the ticket). For further reading, try http://demo.script.aculo.us/ajax/autocompleter, http://griffinm.wordpress.com/2007/06/27/using-autocomplete-and-scriptaculous/, http://metapundit.net/sections/blog/ajax_autocomplete_with_scriptaculous, http://benpetro.blogspot.com/2007/09/implementing-ajaxautocompleter-into.html, and http://www.simpltry.com/2007/01/30/ajaxautocompleter-dynamic-parameters/.