The Sirportly Query Language

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

SELECT

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.idInternal ID
tickets.subjectThe subject
tickets.reply_due_atThe time the next reply is due
tickets.resolution_due_atThe time a resolution is due
tickets.message_idThe ID of the email or tweet message that opened this ticket
tickets.submitted_atThe time the ticket was submitted
tickets.updated_atThe time the ticket was last updated
tickets.last_update_posted_atThe time the last update was posted to the ticket
tickets.update_countThe number of updates
tickets.last_respondantThe last user to update the ticket
tickets.referenceThe reference number
tickets.first_response_timeThe number of minutes before the first response
tickets.first_resolution_timeThe number of minutes before the ticket was first marked as resolved
tickets.resolution_timeThe number of minutes before the last time the ticket was marked as resolved
tickets.status_typeThe 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_typeThe source type of the ticket (ApiToken, SupportCentre, IncomingAddress etc)
Tags
ticket_tags.tagThe ticket tag.
Custom Fields
tickets.custom_field.myfieldThe value of custom field 'myfield'.
Ticket Updates
ticket_updates.idInternal ID
ticket_updates.submitted_atThe time the update was posted
ticket_updates.subjectThe subject of an email update
ticket_updates.messageThe content of the update
ticket_updates.privateIs this a private (internal only) update? 0 for published updates, 1 for private updates.
ticket_updates.author_typeThe type of author. 'User' for a staff member, or 'Contact' for a customer.
ticket_updates.minutes_since_reply_dueNumber of minutes after the reply was due was this posted?
ticket_updates.minutes_since_resolution_dueNumber of minutes after a resolution was due was this posted?
ticket_updates.minutes_since_submissionNumber of minutes after the ticket was created was this posted?
ticket_updates.minutes_since_last_replyNumber of minutes after the last reply was this posted?
ticket_updates.from_nameThe name of the author
ticket_updates.from_addressThe email address of the author
ticket_updates.authenticatedWas this an authenticated post? 0 for unauthenticated, 1 for authenticated.
ticket_updates.delivery_statusThe delivery status of the the reply, can be MessageBounced, MessageDeliveryFailed, or nil. A nil status means the message was delivered successfully.
Users
users.idInternal ID
users.usernameThe username
users.first_nameThe first name
users.last_nameThe last name
users.full_nameThe first name and last name, joined with a space
users.email_addressThe email address
users.enabledWhether the user is enabled or disabled
users.time_zoneThe user's configured timezone
users.admin_accessWhether the user has access to the administrative interface
users.reporting_accessWhether the user has access to the reporting interface
users.tickets_accessWhether the user has access to the tickets interface
Teams
teams.idInternal ID
teams.nameThe name of the team
Customers
contacts.idInternal ID
contacts.nameThe full name
contacts.companyThe company
contacts.phone_numberThe telephone number
contacts.timezoneThe contact's timezone
contacts.referenceThe contact's reference
Contact Methods
contact_methods.idInternal ID
contact_methods.method_typeOne of 'email' or 'twitter'
contact_methods.dataThe address of the contact, either an email address or twitter handle
Statuses
statuses.idInternal ID
statuses.nameThe name of the status
statuses.colourThe colour as a 6-character hex code
statuses.status_typeThe general status: 0 for Open tickets, 1 for Closed tickets, 2 for On Hold tickets.
Priorities
priorities.idInternal ID
priorities.nameThe name of the priority
priorities.colourThe colour as a 6-character hex code
priorities.positionThe priority's rank as a number
Departments
departments.idInternal ID
departments.nameThe name of the department
departments.permalinkThe simple name used in URLs
departments.descriptionThe brand name and department name, joined with a space
Brands
brands.idInternal ID
brands.nameThe name of the brand
SLAs
slas.idInternal ID
slas.nameThe name of the SLA
slas.reply_inThe number of minutes within which a ticket should receive a reply
slas.resolution_inThe 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.

COUNTThe total number of records
AVERAGE column_nameFor a numerical column, the mean value
SUM column_nameFor a numerical column, the sum of all values (the total)

FROM

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.

ticketsRetrieve information about tickets within the system.

WHERE

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.

Modifier

This is optional and allows the column to be modified prior to comparison. It is primarily used when matching parts of dates and times.

DATECompares a timestamp as a date only, for example 2012-01-01
TIMECompares a timestamp as a time only, for example 12:30:00
HOURCompares a numeric hour for a timestamp, ie. 0-23
MONTHCompares a numeric month for a timestamp, ie. 1-12
MONTHNAMECompares the name of a month, eg. January
MONTHANDYEARCompares a timestamp as a Month and Year, ie. July 2014
DAYOFMONTHCompares a numeric day of a month from a timestamp, ie. 1-31
DAYOFWEEKCompares a numeric day of the week from a timestamp, ie. 1-7 (Sunday = 1)
DAYNAMECompares the name of the day of the week from a timestamp, ie. Monday
WEEKCompares a numeric week of the year from a timestamp, ie. 1-52
YEARCompares a numeric year from a timestamp, eg. 2012
QUARTERCompares a numeric quarter from a timestamp, ie. 1-4

Column name

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.

Operator

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
INSee Value below
IN THEAlias of IN

Value

Values can be specified in several ways.

123A simple numerical value
"text"A string of text enclosed in double quotes
number.[minutes|hours|days|weeks|months|years].agoCalculate a time in the past automatically
number.[minutes|hours|days|weeks|months|years].from.nowCalculate 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].agoCalculate a time in the past starting at the beginning of the previous time unit
start.of.[number].[minutes|hours|days|weeks|months|years].from.nowCalculate a time in the future starting at the beginning of the next time unit
end.of.[number].[minutes|hours|days|weeks|months|years].agoCalculate a time in the past starting at the end of the previous time unit
end.of.[number].[minutes|hours|days|weeks|months|years].from.nowCalculate a time in the future starting at the end of the next time unit

GROUP BY

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

ORDER BY

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

LIMIT

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

Proudly powered by Katapult. Running on 100% renewable energy.