Kayako reports gives you the power to define the various types of functions and operators in your KQL statement to get the required information from your helpdesk data.
Almost all of the MySQL functions and operators can be used along with your own custom fields in Kayako.
In this article, we'll walk you through the different categories of KQL functions and operators along with sample KQL statements.
The KQL functions are categorized in the different categories based on their usage in the report.
Conditions and Operators
||Report to include all past chats that do belong to the Technical Support department.|
||Report to include all tickets that do not belong to the Sales department.|
||Report to include all tickets that are in one of the following departments: Sales or Support or Billing.|
||Report to include tickets that are not set to the statuses Closed or In Progress.|
||Report to include all users whose email address matches '%kayako.com', where '%' means anything value.|
||As above, but will include users where the email address does not match '%hotmail.com'.|
||Report to include tickets that are in the Sales department and are set to the status Open.|
||As above, but will include tickets that are in the Sales department or the Support department.|
|<, >, <=, >=||Less than, Greater than, etc||
||Includes all tickets that have more than 5 replies and were created in the last month.|
|+, -, *, /||Add, subtract, multiply, divide||N/A||N/A|
|COUNT()||Counts the number of matching results||
||Produces a count all of the tickets that were created yesterday, grouped by department.|
|IF()||Checks if a condition is true and produces a result||N/A||N/A|
|CUSTOMFIELD()||Returns the value of matching custom field||
||Produces a list of all ticket custom fields for tickets the value of the 'Last check date' custom field of which is in last month.|
CUSTOMFIELD() function accepts three arguments:
- Source (uses report primary source if omitted)
- Custom field group title (looks in all available groups for this source if omitted)
- Custom field title (e.g. 'Last check date'), custom field name (e.g. '6nvjvi53lbh2') or * (all custom fields).
It is recommended to configure Kayako and MySQL to use the same time zone for date conversion accuracy for custom fields of the Date type.
|SUM()||Calculates the sum of matching results||
||Produces a matrix of the total billable time logged for this week, displayed by staff user and by day.|
|AVG()||Calculates the average of matching results||
||Produces a matrix of the average ticket ratings for the month, displayed per ticket owner.|
|MAX()||Returns the maximum value from matching results||
||Produces a list of staff users and their corresponding best (max) rated ticket for the week.|
|MIN()||Returns the minimum value from matching results||
||As above, but displays the worst (min) rated ticket for the week.|
NOTE: MySQL's math functions are also supported.
Date and Time Functions
|MKTIME()||Converts a timestamp into a unixtime.
NOTE: Uses PHP's mktime() syntax.
|FROM_UNIXTIME()||Converts a unixtime into a human readable timestamp.|
|DATENOW()||Returns the current unixtime.|
|LAST_DAY()||Takes a timestamp returns the corresponding value for the last day of that month (e.g. 31 for October).|
|DATEDIFF()||Calculates the difference in days between two timestamps.|
|MONTHRANGE ()||Calculates the dates in between of a range.|
NOTE: MySQL's date and time functions are also supported.
Date and Time Shortcuts
Date and Time Selectors
For various value types, we have implemented Selectors in KQL. Selectors are best demonstrated by an example below:
SELECT AVG('Chats.Wait Time') FROM 'Chats' WHERE 'Chats.Type' = 'User' AND 'Chats.Creation Date' = ThisMonth() GROUP BY X('Chats.Creation Date':MonthName), Y('Chats.Creation Date':DayName)
'Chats.Creation Date' is a value that represents a date. You can specify 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 report, without any complicated syntax.
|:Day||The day number||
|:DayName||The day name (ie Thursday)||
|:Minute||The minute value||
|:Hour||The hour value||
|:Week||The week number (out of 52)||
|:WeekDay||The weekday index, starting from 0 (Monday)||
|:Month||The month number (out of 12)||
|:MonthName||The month name (ie December)||
|:Quarter||The quarter number (out of 4)||
|:Year||The year number||