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 Show Query button to see its DPQL query. The Builder tab gives you a guided view; the Query tab lets you see the DPQL in plain text form.

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]

{UNION another 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.

For example, suppose you want to produce a table like this of all the tickets with a status of "awaiting agent":

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:

You can use an alias to specify a better name for a column.

DPQL_COUNT() AS 'Total Tickets'

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

FROM tickets
SELECT tickets.id

you’d get a table with every ticket’s ID - not very useful.

But with:

FROM tickets
SELECT tickets.id
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

will return all tickets that have a label (i.e. the label value does not equal 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:

with a separate table for each agent.

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 into table rows or bars in the chart:

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-%'
)

UNION

The UNION keyword is used to combine the result-set of two or more SELECT statements. For example:

SELECT tickets.id FROM (
  (SELECT tickets.id FROM tickets)
  UNION
  (SELECT tickets.id FROM tickets)
) as t1

Item IDs

Note that if you want your query to refer to a particular item (e.g. one of your departments), you will often refer to the item by its ID number.

You can display item IDs within the admin interface. Click View Options in the top left of the toolbar and enable Show IDs next to items.

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:

Aliasing expressions with AS

Sometimes one of the columns returned in the results of your report will be the result of a complex expression. For example, you might have a report with:

SELECT DPQL_PERCENT(tickets.total_to_first_reply < 3600)

It’s not very readable to show this as the column header:

You can change how the expression is displayed using AS.

After the expression, use AS 'string', where string is a readable name for the resulting column.

For example,

SELECT DPQL_PERCENT(tickets.total_to_first_reply < 3600) AS 'Percentage within 1 hour'

This results in more readable output on the table:

The readable name is also used on graphs.

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'      

Accessing custom fields

Several content types in Deskpro, including tickets and articles, support custom fields. You can access these fields in custom reports using the following syntax.

Each custom field has an ID number. In the admin interface, click View Options in the top left of the toolbar and enable Show IDs next to items.

You will then see the ID number in the relevant part of the admin interface. For example, for custom ticket fields, look in Tickets > Fields. Make note of the ID as it will be needed in your query.

Once you have the ID, you can reference the value of a particular field via custom_data[#]. For example, to refer to a custom ticket field with ID number 12, you would use tickets.custom_data[12].

This query will give a count of all tickets created this month per value for the custom field with ID 12:

    SELECT DPQL_COUNT() AS 'Total Tickets'
    FROM tickets
    WHERE tickets.date_created = %THIS_MONTH%
    GROUP BY tickets.custom_data[12]

This will list the tickets created this month, split by the values of the custom field with ID 12:

    SELECT tickets.id, tickets.subject, tickets.person
    FROM tickets
    WHERE tickets.date_created = %THIS_MONTH%
    SPLIT BY tickets.custom_data[12]

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.

Each variable has two components:

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