Anatomy of a DPQL query

Reports in the report builder are written using the Deskpro Query Language, known as DPQL. It is similar to SQL, but you don’t need to know SQL to use it.

Select any Stat in the Stat Builder and click the Edit Stat button to see its DPQL query.

DPQL queries consists of a series of clauses:

SELECT select expression
FROM database table
[WHERE conditions]
[SPLIT BY split fields]
[GROUP BY group fields]
[ORDER BY order fields]
[LIMIT amount [OFFSET offset amount]]

[LAYER WITH another query]

[IN subquery]

• Clauses in [square brackets] are optional.

• Italic text is a placeholder for a value that you enter.

SELECT

The SELECT clause works with the WHERE clause to define what information you want your report to include. It often contains field references which specify the data you want to retrieve from the table you choose in the FROM clause.

Your SELECT clause would be a comma-separated list of column references:

SELECT tickets.id, tickets.subject, tickets.person, tickets.department, tickets.date_created, tickets.agent

The SELECT clause can also use DPQL functions.

For example, suppose you didn’t want a detailed table of all matching tickets, just the total number. You would use the DPQL_COUNT() function to count the total number of matching tickets.

SELECT DPQL_COUNT()

In table output, the header text for a column is automatically derived from the SELECT used to produce the column. This may not always result in a good column name; for example, if you just used SELECT DPQL_COUNT(), you’ll produce a table like this:

DPQL_COUNT() AS 'Total Created'

would make the result display like this:

FROM

The MySQL database which stores all your helpdesk data is organised into a series of tables. For example, the tickets table stores information about tickets.

The FROM clause specifies which database table your DPQL query is asking about. You can only pick one table.

All column references in the query must start with the table name specified in the FROM clause.

The tables available are listed here.

WHERE

The conditions in the optional WHERE clause are used to limit what data is displayed or used in a calculation.

For example, if your query was

SELECT tickets.id
FROM tickets

you’d get a table with all ticket’s ID.

But with:

SELECT tickets.id
FROM tickets
WHERE tickets.status = 'awaiting_agent'

you’d get a table with the IDs of just the tickets with a status of Awaiting Agent.

Conditions can be joined by operators such as AND, OR, NOT, IN and parentheses (brackets) to make complex expressions.

IN is useful if you want to match any one of a number of different values. For example:

WHERE tickets.status IN ('awaiting_agent','awaiting_user')

will return all tickets with either Awaiting Agent or Awaiting User status.

tickets.labels.label != NULL

SPLIT BY

The optional SPLIT BY clause enables you split the results into separate tables/graphs by providing split field values.

For example, if you wanted to display each agent’s matching tickets for a query in a separate table, you’d use:

SPLIT BY tickets.agent

and the result would look like this:

You can use multiple split fields by providing a comma-separated list of expressions. This will result in separate tables for each combination of of the fields you provide, with separate tables for Agent A/Department A, Agent A/Department B, Agent B/Department A, etc.

GROUP BY

The GROUP BY clause is used to group records that have the same values for the specified fields.

For example, you can group by ticket labels:

SELECT DPQL_COUNT() AS 'Total Tickets'
FROM tickets
WHERE tickets.status = 'awaiting_agent'
GROUP BY tickets.labels

In the output, tickets with the same label are grouped together:

You can also use groups to do calculations such as determining totals and averages.

The group by fields will automatically be displayed in the resulting table, so there is no need to add them to the SELECT clause.

As in the SELECT clause, group fields can be aliased with AS 'Label' to change the name of the table header row.

To create a matrix table, use the DPQL_MATRIX() function in the GROUP BY clause to specify two groups. The first specifies the values going across the top of the table, while the second controls the values going down the left side.

ORDER BY

The ORDER BY clause determines how the rows will be ordered when they are returned. If no order is given, the results will be displayed in an undefined order.

The order fields are comma-separated expressions. Ordering will happen across the fields from left to right (ordering by the first expression, then using the second to resolve ties, and so on). Each expression may optionally have ASC or DESC appended to it to control whether ordering is in ascending or descending order. (Ascending is the default if no direction is specified).

For example:

ORDER BY SUM(tickets.total_user_waiting) DESC

would order a list of users or organizations by their total waiting time, with the highest waiting time at the top.

The ORDER BY clause can access aliases that were specified in the SELECT or GROUP BY clauses using the syntax @'alias' (for example: @'Total Tickets'). Referencing an alias causes the results to be ordered as if you had written the aliased expression in the ORDER BY clause.

For example, suppose you want to make a table showing how many tickets each agent has, sorted in descending order:

SELECT DPQL_COUNT() AS 'Tickets'
FROM tickets
GROUP BY tickets.agent
ORDER BY @'Tickets' DESC

LIMIT / OFFSET

The LIMIT clause enables you to limit the number of rows returned. If you don’t specify a limit amount, a default limit of 2500 will be used to ensure correct operation.

You can use the OFFSET clause to skip over a certain number of rows before returning the LIMIT amount of rows. The OFFSET defaults to 0.

LAYER WITH

LAYER WITH allows you to combine two different queries into one. For example:

SELECT DPQL_COUNT(*), tickets.department
FROM tickets
WHERE tickets.date_created = %TODAY%
GROUP BY tickets.department

LAYER WITH

SELECT DPQL_COUNT(*), tickets.department
FROM tickets
WHERE tickets.date_created = %YESTERDAY%
GROUP BY tickets.department

IN

You can use IN to create subqueries. For example:

SELECT tickets.id
FROM tickets
WHERE tickets.id IN (
    SELECT tickets.id
    FROM tickets
    WHERE tickets.ref LIKE 'AAAA-%'
)

General expression format

Most clauses of a DPQL statement accept a general expression format. You can use expressions to carry out more complicated queries.

Complex expressions are made up of smaller, simpler expressions. Expressions are made up of the following components:

Date and time references

There are two ways to reference dates and times in your queries:

• absolute dates/times: e.g. 31st October 2013, or 11am on 1 May 2014

• relative time periods using placeholders: e.g. %LAST_WEEK%

To avoid confusion, it’s best to avoid combining absolute and relative dates/times in one query.

Absolute dates and times

Absolute dates/times can be referenced in two formats:

YYYY-MM-DD - e.g. 2013-10-31; refers to a date only. This implicitly has a time of 00:00:00 of the specified day.

YYYY-MM-DD HH:MM:SS - e.g. 2012-10-31 23:35:52; refers to a date and a specific time in 24-hour format.

Note that you must specify these dates in the UTC timezone - however, the results returned will be shown adjusted to your timezone (as set in the Preferences section of the agent interface).

This is an example DPQL query to list the tickets created from October 1st to 15th, 2012 in UTC:

   SELECT tickets.id
    FROM tickets
    WHERE tickets.date_created >= '2012-10-01'
    AND tickets.date_created < '2012-10-16'

In date/time comparisons, > (greater than) matches dates/times that are later, and < (less than) matches dates/times earlier.

You can add or subtract periods of time using the MySQL INTERVAL argument. For example:

'2012-10-01' + INTERVAL 2 WEEK

means a date/time two weeks after October 1st 2012.

You can use INTERVAL 4 HOUR, INTERVAL 3 DAY, INTERVAL 1 MONTH, INTERVAL 2 YEAR etc.

This is useful when adjusting absolute dates to match your timezone. If you wanted to adjust the above example query to find tickets created from October 1st to 15th Eastern Standard Time, you could change it to:

    WHERE tickets.date_created >= '2012-10-01' + INTERVAL 5 HOUR
    AND tickets.date_created < '2012-10-16' + INTERVAL 5 HOUR

This adjusts the times from UTC to 5 hours later, ie EST.

A timezone that is behind UTC needs the time difference added to the comparison date/time; a timezone that is ahead needs the time difference subtracted.

Relative dates with placeholders

It’s often more useful to have a report that matches helpdesk data for a relative time period, e.g. the current week or the last month, rather than specific dates.

You can write a report like this using date placeholders such as %LAST_WEEK%.

For example, if your WHERE clause is:

WHERE tickets.date_created = %PAST_24_HOURS%

it will match all the tickets created within the 24 hours before you run the query.

Note that you use = (equals sign) with placeholders, not < or > as you do with absolute dates/times.

Placeholders use your timezone, as set in your agent account Preferences.

For example, if you run a query to find all the tickets created %TODAY%, it will match all the tickets since the current day began in your timezone.

If there isn’t a placeholder for the interval you need, you can use the SQL NOW function to get the current date, then subtract an INTERVAL.

List of date placeholders

Remember, placeholders use your timezone, as set in your agent account Preferences.

If you have agents in different timezones, they can get different results from queries that use date placeholders.

Dates from custom fields

If you’re retrieving a date stored in a custom field, it is stored as an integer timestamp rather than real date types, so you need to pass it through the FROM_UNIXTIME function first, e.g.:

SELECT
    DATE_FORMAT(FROM_UNIXTIME(tickets.custom_data[1]), '%Y-%m-%d') AS 'Date'

List of functions

A large number of functions are also available that have the exact same behavior as their :

'ABS'
'ACOS'              
'ADDDATE'          
'ADDTIME'          
'ASCII'            
'ASIN'              
'ATAN'              
'ATAN2'            
'AVG'              
'BIN'              
'BIT_AND'          
'BIT_COUNT'        
'BIT_LENGTH'      
'BIT_OR'          
'BIT_XOR'        
'CEIL'              
'CEILING'          
'CHAR'              
'CHAR_LENGTH'      
'CHARACTER_LENGTH'  
'COALESCE'          
'CONCAT'            
'CONCAT_WS'        
'CONV'            
'COS'              
'COT'              
'CRC32'            
'DATE_FORMAT'    
'DATEDIFF'          
'DAYOFYEAR'        
'DAY'              
'DEGREES'          
'ELT'              
'EXP'              
'EXPORT_SET'        
'FIELD'            
'FIND_IN_SET'      
'FLOOR'            
'FORMAT'            
'FROM_DAYS'        
'FROM_UNIXTIME'    
'GREATEST'          
'GROUP_CONCAT'      
'HEX'              
'IF'                
'IFNULL'            
'INET_ATON'              
'INET_NTOA'      
'INSERT'          
'INSTR'            
'INTERVAL'        
'ISNULL'            
'LAST_DAY'          
'LCASE'            
'LEAST'          
'LEFT'            
'LENGTH'            
'LN'                
'LOCATE'        
'LOG10'            
'LOG2'            
'LOG'              
'LOWER'          
'LPAD'              
'LTRIM'            
'MAKE_SET'        
'MAKEDATE'        
'MAKETIME'          
'MAX'              
'MICROSECOND'      
'MID'              
'MIN'              
'MOD'              
'NOW'              
'NULLIF'          
'OCT'              
'OCTET_LENGTH'      
'ORD'              
'PERIOD_ADD'      
'PERIOD_DIFF'      
'POW'              
'POWER'          
'QUARTER'      
'RADIANS'        
'RAND'              
'REPEAT'            
'REPLACE'          
'REVERSE'          
'RIGHT'            
'ROUND'            
'RPAD'              
'RTRIM'            
'SEC_TO_TIME'      
'SECOND'            
'SIGN'            
'SIN'              
'SOUNDEX'          
'SPACE'            
'SQRT'              
'STDDEV_POP'        
'STDDEV_SAMP'      
'STR_TO_DATE'      
'STRCMP'            
'SUBDATE'          
'SUBSTR'            
'SUBSTRING'        
'SUBSTRING_INDEX'  
'SUBTIME'          
'SUM'              
'TAN'              
'TIME'              
'TIME_FORMAT'      
'TIME_TO_SEC'      
'TIMEDIFF'          
'TIMESTAMP'        
'TO_DAYS'          
'TO_SECONDS'        
'TRIM'              
'TRUNCATE'      
'UCASE'            
'UNHEX'            
'UNIX_TIMESTAMP'    
'UPPER'            
'UTC_DATE'        
'UTC_TIME'          
'UTC_TIMESTAMP'    
'VAR_POP'        
'VAR_SAMP'    
'WEEK'              
'WEEKDAY'          
'WEEKOFYEAR'    
'YEARWEEK'          
'CURRENT_DATE'    
'CURRENT_TIME'      
'CURRENT_TIMESTAMP'
'CONVERT_TZ'      

Variables

When creating custom reports, you can set up specific values to be dynamically replaced by the user’s selection from a pull-down menu.

The same mechanism is used in the built-in reports which have pulldowns to choose a date range, ticket property, grouping field, order, etc.

The title - this is how you define the title of the variable. For example, adding articles.views.date_created = ${date} to a query, would make ${date} the title of the variable for dynamic replacement.

The query - this is where the actual variable is defined to run the correct query. You can build these queries using the variable builder by clicking the 'add variable button'.

The available variables are as follows:

Date Ranges

Possible defaults: 

today, yesterday, this_week, this-month, this_year, last_week, last_month, last_year, past_24_hours, past_7_days, past_30_days, ever

Ticket Statuses

Possible defaults:

awaiting_user, awaiting_agent, unresolved, resolved, hidden, any

Field Groups

Possible defaults:

department, agent, agent_team, person, organization, language, usergency, category, priority, workflow, sla, sla_status, hour_created, day_week_created, day_month_created, month_created, year_created, ticketfield# (for custom ticket fields), personfield# (for custom person fields), orgfield# (for custom organization fields), none

Ordering

Possible defaults:

date_created_asc, date_created_desc, last_agent_reply_asc, last_agent_reply_desc, last_user_reply_asc, last_user_reply_desc, total_waiting_asc, total_waiting_desc