This article services as a reference resource to help you build complex KQL queries.
Conditions and Operators
Operator
|
KQL Example
|
Results
|
=
|
SELECT'Chats.Chat ID'FROMChats WHERE'Chat.Department'= 'Technical Support'
|
Report to include all past chats that do belong to the Technical Supportdepartment.
|
!=
|
SELECT'Tickets.Ticket ID'FROMTickets WHERE'Tickets.Department'!= 'Sales'
|
Report to include all tickets that do not belong to the Sales department.
|
IN
|
SELECT'Tickets.Ticket ID'FROMTickets 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'FROMTickets WHERE'Tickets.Status'NOTIN('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'NOTLIKE'%kayako.com'
|
As above, but will include users where the email address does not match '%hotmail.com'.
|
AND
|
SELECT'Tickets.Ticket ID'FROMTickets 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'FROMTickets 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'FROMTickets 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
|
Functions
Function
|
Description
|
KQL Example
|
Results
|
COUNT()
|
Counts the number of matching results
|
SELECTCOUNT('Tickets.Ticket ID') FROMTickets WHERE'Tickets.Creation Date'= Yesterday() GROUPBY'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
|
Custom fields
Function
|
Description
|
KQL Example
|
Results
|
CUSTOMFIELD()
|
Returns the value of matching custom field
|
SELECT'Tickets.Ticket Mask ID', CUSTOMFIELD(*) FROM'Tickets'WHERECUSTOMFIELD('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
|
SELECTSUM('Ticket Billing.Time Spent') FROM'Ticket Billing'WHERE'Ticket Billing.Creation Date'>= ThisWeek() GROUPBYX('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
|
SELECTAVG('Rating Results.Score') FROM'Rating Results', 'Tickets'WHERE'Ratings.Type'= 'Tickets'AND'Tickets.Creation Date'= ThisMonth() GROUPBYX('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
|
SELECTMAX('Rating Results.Score') FROM'Rating Results', 'Tickets'WHERE'Tickets.Creation Date'= ThisWeek() GROUPBY'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
|
SELECTMIN('Rating Results.Score') FROM'Rating Results', 'Tickets'WHERE'Tickets.Creation Date'= ThisWeek() GROUPBY'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.
|
NOTE: MySQL's date and time functions are also supported.
Date and time shortcuts
Shortcut
|
KQL Example
|
Yesterday()
|
SELECTCount('Tickets.Ticket ID') FROMTickets 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
Selector
|
Description
|
KQL Example
|
:Day
|
The day number
|
SELECT'Tickets.Ticket ID'FROMTickets WHERE'Tickets.Resolution Due Date'= Today() GROUPBY'Tickets.Creation Date':Day
|
:DayName
|
The day name (ie Thursday)
|
... GROUPBY'Tickets.Creation Date':DayName
|
:Minute
|
The minute value
|
... GROUPBY'Tickets.Creation Date':Minute
|
:Hour
|
The hour value
|
... GROUPBY'Tickets.Creation Date':Hour
|
:Week
|
The week number (out of 52)
|
... GROUPBY'Tickets.Creation Date':Week
|
:WeekDay
|
The weekday index, starting from 0 (Monday)
|
... GROUPBY'Tickets.Creation Date':WeekDay
|
:Month
|
The month number (out of 12)
|
... GROUPBY'Tickets.Creation Date':Month
|
:MonthName
|
The month name (ie December)
|
... GROUPBY'Tickets.Creation Date':MonthName
|
:Quarter
|
The quarter number (out of 4)
|
... GROUPBY'Tickets.Creation Date':Quarter
|
:Year
|
The year number
|
... GROUPBY'Tickets.Creation Date':Year
|
Kelly O'Brien