Your Kayako Classic helpdesk contains a variety of out-of-the-box reports, so if you're not comfortable writing your own reports using the Kayako Query Language, don't worry. See our Introduction to building and running reports article for more information.
A line of KQL (a KQL statement) specifies what information to include in your report, on what conditions and how to display the information. Every report in your Kayako Classic helpdesk is defined by a KQL statement, and each report can be opened and its KQL statement tweaked or copied as a template for another report.
If you are familiar with SQL, you can treat KQL almost exactly the same (except we have added some Kayako-specific features).
Basic KQL Statement
The most basic KQL statement looks like this:
SELECT'Tickets.Ticket Mask ID', 'Tickets.Subject'FROM'Tickets'
This example statement is valid contains the complete minimum components required for a report. The result of this report will be a list of every ticket and its corresponding ID and subject in your helpdesk.
The statement has two components:
- SELECT - Every statement starts with a SELECT. With it you are telling Reports what fields and information to fetch from your helpdesk.
- FROM - Each SELECT is proceeded by a FROM, which explicitly tells Reports the data sources you wish to use in your report. As we are only using the Tickets source in this example, this is the only one we need to specify.
Sources and Fields
If you're familiar with database terminology, a 'source' is a table and a 'field' is a field.
In the example above we had two things - a source and a couple of fields. Every source has a list of fields.
SELECT'Tickets.Ticket Mask ID', 'Tickets.Subject', 'Users.Fullname'FROM'Tickets', 'Users'
In this new example, we have the following sources:
- Tickets
- Users
And the following fields:
- Tickets.Ticket Mask ID
- Tickets.Subject
- Users.Fullname
Even though you are asked to select a primary source when creating a new report, it is possible to reference multiple sources within your KQL statement. You don't need to worry about the way this source linking is done - it is automatically taken care of. All you need to do is specify the respective FROMs sources for the fields you are SELECTing.
The Report Writer will automatically suggest the sources you need to include in your statement.
KQL fields
For a complete list of tables and fields used in KQL, refer to KQL tables and Fields (PDF).
Custom Fields
Since 4.52 custom fields can be referenced as follows: 'Source.Custom Fields.Optional Group.Field Name'.
Custom fields can be retrieved in KQL using CUSTOMFIELD() function. For example:
SELECT'Tickets.Ticket Mask ID', 'Tickets.Subject', 'Users.Fullname', CUSTOMFIELD('Tickets', *) FROM'Tickets', 'Users'
The CUSTOMFIELD() function accepts two optional and one mandatory arguments:
- Source (optional)
- Custom field group title (optional)
- Custom field title, custom field name or *
Thus the above KQL will produce a list of all custom fields under the Tickets source. If source is omitted the primary source is used. If custom field group title is omitted a custom field will be searched in all groups of source.
The third argument can be custom field title (e.g. 'Role'), custom field name (e.g. '6nvjvi53lbh2') or * (all custom fields).
It is safe to use CUSTOMFIELD(Source, *) if there are no custom fields associated with source.
Currently custom fields are available for the following sources:
- Users
- User Organizations
- Chats
- Tickets
- Ticket Billing
See also the KQL Condition and Operator Reference.
Conditionally selecting information (WHERE)
The example KQL statement used above:
SELECT'Tickets.Ticket Mask ID', 'Tickets.Subject', 'Users.Fullname'FROM'Tickets', 'Users'
Would produce a report that listed every single ticket in the helpdesk, including the ticket ID, subject and user's name. We can refine this report to include tickets that match some specific criteria.
For example, the following KQL statement includes a WHERE and some conditions. It will only include tickets that belong to the Sales department and are set to the status Open:
SELECT'Tickets.Ticket Mask ID', 'Tickets.Subject', 'Users.Fullname'FROM'Tickets', 'Users'WHERE'Tickets.Department'= 'Sales'AND'Tickets.Status'= 'Open'
Multiple conditions
The AND operator in the example above indicates that tickets must match both conditions. We could also use the OR operator to indicate optional conditions. The example below will produce a report listing tickets set to the status Open that belong to either the Support department or the Sales department:
SELECT'Tickets.Ticket Mask ID', 'Tickets.Subject', 'Users.Fullname'FROM'Tickets', 'Users'WHERE('Tickets.Department'= 'Sales'OR'Tickets.Department'= 'Support') AND'Tickets.Status'= 'Open'
Because we grouped the department condition in brackets, it gets treated as one condition. This KQL statement contains the following conditions:
- ('Tickets.Department' = 'Sales' OR 'Tickets.Department' = 'Support')
AND - 'Tickets.Status' = 'Open'
Only when both of these conditions are met will a ticket be included in our report. For example:
| Ticket ID |
Ticket Status |
Ticket Dept. |
Included in the report? |
Why? |
|---|---|---|---|---|
| #ABC-123-0001 |
Open |
Sales |
 |
Meets both conditions |
| #ABC-123-0002 |
Open |
Billing |
 |
Fails to meet the condition ('Tickets.Department' = 'Sales' OR 'Tickets.Department' = 'Support') |
| #ABC-123-0003 |
Closed |
Support |
 |
Fails to meet the condition 'Tickets.Status' = 'Open' |
| #ABC-123-0004 |
Open |
Support |
 |
Meets both conditions |
For a list of conditions and operators (including IN and LIKE), see our KQL Condition and Operator Reference.
Functions
For a list of functions, see the KQL Condition and Operator Reference.
Shortcuts
For a list of shortcuts, see the KQL Condition and Operator Reference.
ORDER BY (Ordering Results)
The ORDER BY operator orders information in tabular reports. The following KQL statement will produce a list of tickets including the ticket ID, subject and priority. The list of tickets will be sorted by priority:
SELECT'Tickets.Ticket Mask ID', 'Tickets.Subject', 'Tickets.Priority'FROM'Tickets'WHERE'Tickets.Status'= 'Open'AND'Tickets.Creation Date'= Today() ORDERBY'Tickets.Priority'
An ORDER BY operator can be used with a GROUP BY or MULTIGROUP BY operator, as follows:
SELECT'Tickets.Ticket Mask ID', 'Tickets.Subject', 'Tickets.Priority'FROM'Tickets'WHERE'Tickets.Status'= 'Open'AND'Tickets.Creation Date'= Today() MULTIGROUP BY'Tickets.Department'ORDERBY'Tickets.Priority'
Grouping Results (GROUP BY)
The GROUP BY operator groups results together. It's main purpose is for use in a summary table type of report. Whereas a regular tabular report (which would be produced from the example KQL statements up until now) produces lists of information, a summary table consolidates information.
Let's say that we want to count all of the Open in the helpdesk. Our KQL statement would look like this:
SELECTCOUNT(*) FROM'Tickets'WHERE'Tickets.Status'= 'Open'AND'Tickets.Department'
And would produce something fairly basic, which looked like this:
Using GROUP BY we could break this count down by department, so we'll retrieve the count of Open tickets across our departments. The revised KQL statement is:
SELECTCOUNT(*) FROM'Tickets'WHERE'Tickets.Status'= 'Open'AND'Tickets.Department'GROUPBY'Tickets.Department'
And would produce a report which looked like this:
It is possible to GROUP BY multiple fields to break the summary table report down into even more detail. The following KQL statement will GROUP BY ticket department and then by priority:
SELECTCOUNT(*) FROM'Tickets'WHERE'Tickets.Status'= 'Open'AND'Tickets.Department'GROUPBY'Tickets.Department', 'Tickets.Priority'
For more information on report types and layouts, see Report Types.
GROUP BY X(), Y() for matrix reports
Since 4.52 X() and Y() are also supported as post-modifiers, e.g.,GROUP BY 'Tickets.Department') X, 'Tickets.Owner' Y.
In order to produce a matrix report (see Report Types), a KQL statement must specify a GROUP BY X(), Y() (because each matrix report has at least one row of headings and one column of row labels).
For example, the following KQL statement will produce a matrix report showing the count of Open tickets in departments (X, the headings) against ticket owners (Y, the rows):
SELECTCOUNT(*) FROM'Tickets'WHERE'Tickets.Status'= 'Open'GROUPBYX('Tickets.Department'), Y('Tickets.Owner')
We can created a nested matrix report by adding more X()*s and Y()s to the *GROUP BY. So, taking the same example above, we can further break the ticket counts down by priority:
SELECTCOUNT(*) FROM'Tickets'WHERE'Tickets.Status'= 'Open'GROUPBYX('Tickets.Department'), X('Tickets.Priority'), Y('Tickets.Owner')
To produce:
MULTIGROUP BY
The MULTIGROUP BY operator is used to produce a list of tables within one report. For example, to the following KQL statement will produce a list of user organization's phone numbers, MULTIGROUPed BY country:
SELECT'User Organizations.Name', 'User Organizations.Phone', 'User Organizations.Country'FROM'User Organizations'MULTIGROUP BY'User Organizations.Country'
A MULTIGROUP BY cannot be used with the GROUP BY operator.
Specifically, the MULTIGROUP BY operator is used to produced grouped tabular reports. For more information, see Report Types.
Selectors
For various value types, we have implemented a feature called Selectors. Selectors are best demonstrated by an example:
SELECTAVG('Chats.Wait Time') FROM'Chats'WHERE'Chats.Type'= 'User'AND'Chats.Creation Date'= ThisMonth() GROUPBYX('Chats.Creation Date':MonthName), Y('Chats.Creation Date':DayName)
‘Chats.Creation Date’ is a value that represents a date. You can attach various Selectors to this using a colon, following by the selector such as DayName and MonthName. In this case, these Selectors allow you to select parts of a date or render dates in different ways in your reports, without any complicated syntax.
For a list of Selectors, see the KQL Condition and Operator Reference.
Variables
Any expression from the SELECT clause, for which an alias was defined using the AS operator, can be referenced from any other clause (except FROM) using the syntax $'Alias Name'.
Date and Time in Reports
There are two types of date and time representations used in the helpdesk:
- Unix time - Rather than storing dates and times as long text, like '2011-12-01 13:10:22', unixtime is the number of seconds since January 1, 1970. This is how Kayako stores date and time values in the helpdesk. It's handy for computers, but not so great for reading.
- Timestamp - This is a more readable way of representing date and time, and looks like the following: '2011-12-01 13:10:22'.
When a date and time value is selected in a report, all of the calculation is done when the time value is unixtime. When your report is run and the results are displayed, the unixtime is converted to a timestamp.
Kelly O'Brien