Overview

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.

Information

The KQL functions are categorized in the different categories based on their usage in the report. 

Conditions and Operators

Operator	KQL Example	Results
=	SELECT 'Chats.Chat ID' FROM Chats WHERE 'Chat.Department'= 'Technical Support'	Report to include all past chats that do belong to the Technical Support department.
!=	SELECT 'Tickets.Ticket ID' FROM Tickets WHERE 'Tickets.Department'!= 'Sales'	Report to include all tickets that do not belong to the Sales department.
IN	SELECT 'Tickets.Ticket ID' FROM Tickets WHERE 'Tickets.Department' IN ('Sales', 'Support', 'Billing')	Report to include all tickets that are in one of the following departments: Sales or Support or Billing.
NOT IN	SELECT 'Tickets.Ticket ID' FROM Tickets WHERE 'Tickets.Status' NOT IN ('Closed', 'In Progress')	Report to include tickets that are not set to the statuses Closed or In Progress.
LIKE	SELECT 'Users.Fullname', 'Users.User Organization' FROM 'User Emails', 'Users' WHERE 'User Emails.Email' LIKE '%kayako.com'	Report to include all users whose email address matches '%kayako.com', where '%' means anything value.
NOT LIKE	SELECT 'Users.Fullname', 'Users.User Organization' FROM 'User Emails', 'Users' WHERE 'User Emails.Email' NOT LIKE '%kayako.com'	As above, but will include users where the email address does not match '%hotmail.com'.
AND	SELECT 'Tickets.Ticket ID'FROM Tickets WHERE 'Tickets.Department'= 'Sales'AND 'Tickets.Status'= 'Open'	Report to include tickets that are in the Sales department and are set to the status Open.
OR	SELECT 'Tickets.Ticket ID'FROM Tickets WHERE 'Tickets.Department'= 'Sales' OR 'Tickets.Department'= 'Support'	As above, but will include tickets that are in the Sales department or the Support department.

 

Numerical Operators

Operator	Description	KQL Example	Results
<, >, <=, >=	Less than, Greater than, etc	SELECT 'Tickets.Ticket ID', 'Tickets.Subject' FROM Tickets WHERE 'Tickets.Reply Count'> 5 AND 'Tickets.Creation Date'= LastMonth()	Includes all tickets that have more than 5 replies and were created in the last month.
+, -, *, /	Add, subtract, multiply, divide	N/A	N/A

 

Functions

Function	Description	KQL Example	Results
COUNT()	Counts the number of matching results	SELECT COUNT('Tickets.Ticket ID') FROM Tickets WHERE 'Tickets.Creation Date'= Yesterday() GROUP BY 'Tickets.Department'	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

 

Custom Fields

Function	Description	KQL Example	Results
CUSTOMFIELD()	Returns the value of matching custom field	SELECT 'Tickets.Ticket Mask ID', CUSTOMFIELD(*) FROM 'Tickets'WHERE CUSTOMFIELD('Last check date') = LastMonth()	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.

 

The 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.

 

Math Functions

Function	Description	KQL Example	Results
SUM()	Calculates the sum of matching results	SELECT SUM('Ticket Billing.Time Spent') FROM 'Ticket Billing' WHERE 'Ticket Billing.Creation Date'>= ThisWeek() GROUP BY X('Ticket Billing.Creation Date':DayName), Y('Ticket Billing.Worker')	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	SELECT AVG('Rating Results.Score') FROM 'Rating Results', 'Tickets' WHERE 'Ratings.Type'= 'Tickets'AND'Tickets.Creation Date'= ThisMonth() GROUP BY X('Ratings.Title'), Y('Tickets.Owner')	Produces a matrix of the average ticket ratings for the month, displayed per ticket owner.
MAX()	Returns the maximum value from matching results	SELECT MAX('Rating Results.Score') FROM 'Rating Results', 'Tickets' WHERE 'Tickets.Creation Date'= ThisWeek() GROUP BY'Tickets.Owner', 'Ratings.Title'	Produces a list of staff users and their corresponding best (max) rated ticket for the week.
MIN()	Returns the minimum value from matching results	SELECT MIN('Rating Results.Score') FROM 'Rating Results', 'Tickets' WHERE 'Tickets.Creation Date'= ThisWeek() GROUP BY'Tickets.Owner', 'Ratings.Title'	As above, but displays the worst (min) rated ticket for the week.

 

NOTE: MySQL's math functions are also supported.

 

Date and Time Functions

Function	Description
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

Shortcut	KQL Example
Yesterday()	SELECT Count('Tickets.Ticket ID') FROM Tickets WHERE 'Tickets.Creation Date'= Yesterday()
Today()	... WHERE 'Tickets.Creation Date'= Today()
Tomorrow()	... WHERE 'Tickets.Due Date'= Tomorrow()
Last7Days()	... WHERE 'Tickets.Creation Date'= Last7Days()
LastWeek()	... WHERE 'Tickets.Creation Date'= LastWeek()
ThisWeek()	... WHERE 'Tickets.Due Date'= ThisWeek()
NextWeek()	... WHERE 'Tickets.Due Date'= NextWeek()
LastMonth()	... WHERE 'Tickets.Creation Date'= LastMonth()
ThisMonth()	... WHERE 'Tickets.Resolution Due Date'= ThisMonth()
NextMonth()	... WHERE 'Tickets.Resolution Due Date'= NextMonth()
EndOfWeek()	... WHERE 'Tickets.Due Date'= EndOfWeek()

 

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.

Selector	Description	KQL Example
:Day	The day number	SELECT 'Tickets.Ticket ID' FROM Tickets WHERE 'Tickets.Resolution Due Date'= Today() GROUP BY'Tickets.Creation Date':Day
:DayName	The day name (ie Thursday)	... GROUP BY 'Tickets.Creation Date':DayName
:Minute	The minute value	... GROUP BY 'Tickets.Creation Date':Minute
:Hour	The hour value	... GROUP BY 'Tickets.Creation Date':Hour
:Week	The week number (out of 52)	... GROUP BY 'Tickets.Creation Date':Week
:WeekDay	The weekday index, starting from 0 (Monday)	... GROUP BY 'Tickets.Creation Date':WeekDay
:Month	The month number (out of 12)	... GROUP BY 'Tickets.Creation Date':Month
:MonthName	The month name (ie December)	... GROUP BY 'Tickets.Creation Date':MonthName
:Quarter	The quarter number (out of 4)	... GROUP BY 'Tickets.Creation Date':Quarter
:Year	The year number	... GROUP BY 'Tickets.Creation Date':Year