Proudly powered by Katapult. Running on 100% renewable energy.
Reports are generated using SPQL (SirPortly Query Language). The information below explains the syntax and features of the language.
A query in SPQL is made up of up to 5 parts, some of which are optional. These must be used in order: SELECT AVERAGE first_response_time, brand.name FROM tickets WHERE submitted_at >= 3.months.ago GROUP BY brand.name ORDER BY brand.name
The first part of the query defines a list of columns to be returned. These should be comma-separated and can include either a table name and a column name, for example users.full_name, or a just column name of a field from the table specified in the FROM, for example, submitted_at.
When selecting a column from a table other than the one specified in the FROM section of the query, the appropriate data will automatically be looked up as appropriate. See below for a complete list of available columns.
| Tickets | |
|---|---|
| tickets.id | Internal ID |
| tickets.subject | The subject |
| tickets.reply_due_at | The time the next reply is due |
| tickets.resolution_due_at | The time a resolution is due |
| tickets.message_id | The ID of the email or tweet message that opened this ticket |
| tickets.submitted_at | The time the ticket was submitted |
| tickets.updated_at | The time the ticket was last updated |
| tickets.last_update_posted_at | The time the last update was posted to the ticket |
| tickets.update_count | The number of updates |
| tickets.last_respondant | The last user to update the ticket |
| tickets.reference | The reference number |
| tickets.first_response_time | The number of minutes before the first response |
| tickets.first_resolution_time | The number of minutes before the ticket was first marked as resolved |
| tickets.resolution_time | The number of minutes before the last time the ticket was marked as resolved |
| tickets.status_type | The general status: 0 for Open tickets, 1 for Closed tickets, 2 for On Hold tickets. To obtain the specific status of the ticket, see the statuses table. |
| tickets.source_type | The source type of the ticket (ApiToken, SupportCentre, IncomingAddress etc) |
| Tags | |
| ticket_tags.tag | The ticket tag. |
| Custom Fields | |
| tickets.custom_field.myfield | The value of custom field 'myfield'. |
| Ticket Updates | |
| ticket_updates.id | Internal ID |
| ticket_updates.submitted_at | The time the update was posted |
| ticket_updates.subject | The subject of an email update |
| ticket_updates.message | The content of the update |
| ticket_updates.private | Is this a private (internal only) update? 0 for published updates, 1 for private updates. |
| ticket_updates.author_type | The type of author. 'User' for a staff member, or 'Contact' for a customer. |
| ticket_updates.minutes_since_reply_due | Number of minutes after the reply was due was this posted? |
| ticket_updates.minutes_since_resolution_due | Number of minutes after a resolution was due was this posted? |
| ticket_updates.minutes_since_submission | Number of minutes after the ticket was created was this posted? |
| ticket_updates.minutes_since_last_reply | Number of minutes after the last reply was this posted? |
| ticket_updates.from_name | The name of the author |
| ticket_updates.from_address | The email address of the author |
| ticket_updates.authenticated | Was this an authenticated post? 0 for unauthenticated, 1 for authenticated. |
| ticket_updates.delivery_status | The delivery status of the the reply, can be MessageBounced, MessageDeliveryFailed, or nil. A nil status means the message was delivered successfully. |
| Users | |
| users.id | Internal ID |
| users.username | The username |
| users.first_name | The first name |
| users.last_name | The last name |
| users.full_name | The first name and last name, joined with a space |
| users.email_address | The email address |
| users.enabled | Whether the user is enabled or disabled |
| users.time_zone | The user's configured timezone |
| users.admin_access | Whether the user has access to the administrative interface |
| users.reporting_access | Whether the user has access to the reporting interface |
| users.tickets_access | Whether the user has access to the tickets interface |
| Teams | |
| teams.id | Internal ID |
| teams.name | The name of the team |
| Customers | |
| contacts.id | Internal ID |
| contacts.name | The full name |
| contacts.company | The company |
| contacts.phone_number | The telephone number |
| contacts.timezone | The contact's timezone |
| contacts.reference | The contact's reference |
| Contact Methods | |
| contact_methods.id | Internal ID |
| contact_methods.method_type | One of 'email' or 'twitter' |
| contact_methods.data | The address of the contact, either an email address or twitter handle |
| Statuses | |
| statuses.id | Internal ID |
| statuses.name | The name of the status |
| statuses.colour | The colour as a 6-character hex code |
| statuses.status_type | The general status: 0 for Open tickets, 1 for Closed tickets, 2 for On Hold tickets. |
| Priorities | |
| priorities.id | Internal ID |
| priorities.name | The name of the priority |
| priorities.colour | The colour as a 6-character hex code |
| priorities.position | The priority's rank as a number |
| Departments | |
| departments.id | Internal ID |
| departments.name | The name of the department |
| departments.permalink | The simple name used in URLs |
| departments.description | The brand name and department name, joined with a space |
| Brands | |
| brands.id | Internal ID |
| brands.name | The name of the brand |
| SLAs | |
| slas.id | Internal ID |
| slas.name | The name of the SLA |
| slas.reply_in | The number of minutes within which a ticket should receive a reply |
| slas.resolution_in | The number of minutes within which the ticket should be resolved |
The following additional syntax can be used to group data and is usually used with the GROUP by option described later in this page.
| COUNT | The total number of records |
| AVERAGE column_name | For a numerical column, the mean value |
| SUM column_name | For a numerical column, the sum of all values (the total) |
The FROM part of the query specifies the primary table to retrieve data from. Data requested from other tables in the SELECT part of the query will automatically be looked up and joined with this table as appropriate. Currently, only the tickets table is supported.
| tickets | Retrieve information about tickets within the system. |
The WHERE part of the query is used to filter data. It defines conditions in the format modifier column_name operator value. Multiple conditions can be specified, using the keywords AND and OR as well as parentheses ( ) to set precedence.
This is optional and allows the column to be modified prior to comparison. It is primarily used when matching parts of dates and times.
| DATE | Compares a timestamp as a date only, for example 2012-01-01 |
| TIME | Compares a timestamp as a time only, for example 12:30:00 |
| HOUR | Compares a numeric hour for a timestamp, ie. 0-23 |
| MONTH | Compares a numeric month for a timestamp, ie. 1-12 |
| MONTHNAME | Compares the name of a month, eg. January |
| MONTHANDYEAR | Compares a timestamp as a Month and Year, ie. July 2014 |
| DAYOFMONTH | Compares a numeric day of a month from a timestamp, ie. 1-31 |
| DAYOFWEEK | Compares a numeric day of the week from a timestamp, ie. 1-7 (Sunday = 1) |
| DAYNAME | Compares the name of the day of the week from a timestamp, ie. Monday |
| WEEK | Compares a numeric week of the year from a timestamp, ie. 1-52 |
| YEAR | Compares a numeric year from a timestamp, eg. 2012 |
| QUARTER | Compares a numeric quarter from a timestamp, ie. 1-4 |
This is simply the name of any available column in the same format as in the SELECT part of the query. The column does not need to be present in the SELECT part of the query to filter on it.
A number of operators are available to filter data. Please be aware that numeric operators will only work on numeric and date fields. Using these on text fields (such as custom fields) containing numbers is likely to produce unexpected results.
| = | A simple equality test, will work on numeric of test data |
| != | Not equal, the opposite of = |
| > | Greater than, only useful for numeric data |
| < | Less than, only useful for numeric data |
| >= | Greater than or equal to, only useful for numeric data |
| <= | Less than or equal to, only useful for numeric data |
| IN | See Value below |
| IN THE | Alias of IN |
Values can be specified in several ways.
| 123 | A simple numerical value |
| "text" | A string of text enclosed in double quotes |
| number.[minutes|hours|days|weeks|months|years].ago | Calculate a time in the past automatically |
| number.[minutes|hours|days|weeks|months|years].from.now | Calculate a time in the future automatically |
| IN [LAST|NEXT|THIS].[YEAR|QUARTER|MONTH|WEEK|DAY|HOUR|HALFHOUR] | A range of times relative to now |
| start.of.[number].[minutes|hours|days|weeks|months|years].ago | Calculate a time in the past starting at the beginning of the previous time unit |
| start.of.[number].[minutes|hours|days|weeks|months|years].from.now | Calculate a time in the future starting at the beginning of the next time unit |
| end.of.[number].[minutes|hours|days|weeks|months|years].ago | Calculate a time in the past starting at the end of the previous time unit |
| end.of.[number].[minutes|hours|days|weeks|months|years].from.now | Calculate a time in the future starting at the end of the next time unit |
This allows data to be grouped together and is particularly useful when combined with AVERAGE or COUNT modifiers when producing statistics. This must appear after the WHERE parameter and is followed by a comma-separated list of columns go group the data by. For example, to obtain a count the number of tickets assigned to each user, on a per-department basis, you would run the following:
SELECT departments.description,users.full_name,COUNT FROM tickets GROUP BY department.id,user.id
It is possible to sort the data. The syntax of ORDER BY is identical to GROUP BY and should simply be a comma-separated list of columns to order data by. This will often be the same columns as the GROUP BY statement.
SELECT departments.description,users.full_name,COUNT FROM tickets GROUP BY department.id,user.id ORDER BY departments.description,users.full_name
Finally, it is possible to limit the number of rows returned. The syntax of LIMIT statement is either a single number (the limit), or 2 comma-separated numbers (the offset and the limit).
SELECT tickets.subject LIMIT 20, 10