KQL condition & operator reference
GFI HelpDesk 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 GFI HelpDesk.
In this topic, 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
| Operator | KQL Example | Results |
|---|---|---|
| = | SELECT 'Chats.Chat ID' FROM Chats WHERE 'Chat.Department'= 'Technical Support' | Report to include all past chats that belongs 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 orBilling. |
| 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 '%GFI HelpDesk.com' | Report to include all users whose email address matches '%gfi.com', where '%' means anything value. |
| NOT LIKE | SELECT 'Users.Fullname', 'Users.User Organization' FROM 'User Emails', 'Users' WHERE 'User Emails.Email' NOT LIKE '%GFI HelpDesk.com' | As above, but includes 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 a reset to the status Open. |
| OR | SELECT 'Tickets.Ticket ID'FROM Tickets WHERE 'Tickets.Department'= 'Sales' OR 'Tickets.Department'= 'Support' | As above, but includes 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 (for example, 'Last check date'), custom field name (for example, '6nvjvi53lbh2') or * (all custom fields).
It is recommended to configure GFI HelpDesk 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 atimestampinto aunixtime.NOTE: UsesPHP's mktime() syntax. |
| FROM_UNIXTIME() | Converts aunixtimeinto a human readabletimestamp. |
| DATENOW() | Returns the currentunixtime. |
| LAST_DAY() | Takes atimestampreturns the corresponding value for the last day of that month (for example, 31 for October). |
| DATEDIFF() | Calculates the difference in days between twotimestamps. |
| 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 |