This article is a reference resource to help you build complex KQL queries.
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
|: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||