Introduction to Kayako Query Language (KQL)
Your GFI HelpDesk 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 GFI HelpDesk 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).
The most basic KQL statement looks like this:
This example statement is valid contains the complete minimum components required for a report. The result of this report is 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.
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.
In this new example, we have the following sources:
And the following fields:
- Tickets.Ticket Mask ID
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 automatically suggests the sources you need to include in your statement.
For a complete list of tables and fields used in KQL, refer to KQL tables and Fields (PDF).
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:
The CUSTOMFIELD() function accepts two optional and one mandatory argument:
- Source (optional)
- Custom field group title (optional)
- Custom field title, custom field name or *
Thus the above KQL produces 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 is searched in all groups of source.
The third argument can be custom field title (for example, 'Role'), custom field name (for example, '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:
- User Organizations
- Ticket Billing
See also the KQL Condition and Operator Reference.
The example KQL statement used above:
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 only includes tickets that belong to the Sales department and are set to the status Open:
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 produces a report listing tickets set to the status Open that belong to either the Support department or the Sales department:
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')
- 'Tickets.Status' = 'Open'
A ticket is included in our report only if both of these conditions are met. For example:
|Included in the report?
|Meets both conditions
|Fails to meet the condition ('Tickets.Department' = 'Sales' OR 'Tickets.Department' = 'Support')
|Fails to meet the condition 'Tickets.Status' = 'Open'
|Meets both conditions
The ORDER BY operator orders information in tabular reports. The following KQL statement produces a list of tickets including the ticket ID, subject and priority. The list of tickets is sorted by priority:
An ORDER BY operator can be used with a GROUP BY or MULTIGROUP BY operator, as follows:
The GROUP BY operator groups results together. Its 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:
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:
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:
For more information on report types and layouts, see Report Types.
Since 4.52 X() and Y() are also supported as post-modifiers, for example,
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 produces a matrix report showing the count of Open tickets in departments (X, the headings) against ticket owners (Y, the rows):
We can create 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:
The MULTIGROUP BY operator is used to produce a list of tables within one report. For example, to the following KQL statement produces a list of user organization's phone numbers, MULTIGROUPed BY 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.
For various value types, we have implemented a feature called Selectors. Selectors are best demonstrated by an example:
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
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.
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 GFI HelpDesk 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.