Overview

Kayako Classic has a built-in feature used to create custom reports using the Kayako Query Language (KQL). There are already several pages of reports available out of the box, but whenever a user needs something more unique, they can use KQL to prepare a new type of report.

For instance, a user might need a specific value from all of the tickets such as the method they used to be created or one might need data for a very specific time range that is not available in the default reports.

Generally, the use of KQL in a custom report would suffice; however, there might be some scenarios where obtaining this data is not possible through KQL, and it might be necessary to query the database. This article will cover the relevant areas for each of the approaches along with helpful resources for further review.

Custom Reports in KQL

A line of KQL (a KQL statement) specifies the information to include in your report, on what conditions and how to display the information. Every report in Kayako Classic is defined by a KQL statement, and each report can be opened and its KQL statement tweaked or copied as a template for another report.

If one is familiar with SQL, using KQL would be very similar with some Kayako-specific caveats as mentioned in the references below. Note that the editor for the reports offers autocompleting making it easier to build a custom query. Moreover, it would also throw errors whenever an invalid column or table is used in a query.

As KQL has been thoroughly documented, it is recommended to review the following resources to understand the vast functionality offered by the custom reports/KQL.

• Introduction to Building and Running Reports 
• KQL Condition & Operator Reference 
• Report Types in Kayako Classic 
• Optimizing KQL Reports 

Lastly, one can review the attached document on the KQL tables and columns as a reference when preparing custom queries and to better understand its limitations.

Obtaining data from the SQL Database

While the Kayako Classic database has around 230 tables, only a few of these have been mapped in KQL (around 14 as per the attached article on KQL). Now, most of the tables might not contain information that would be useful on the reports; however, there are a number of tables that could prove helpful in obtaining information that is not available through the UI.

Refer to the attached schema file for a list of all tables and their columns. Additionally, one can find the list of tables and their columns by going to the Admin CP > Database > Table information area in their instance.

Below are some of the tables that might contain information relevant to reports and not accessible through KQL. For such cases, a DB admin would need to prepare a SQL query in order to extract all of the needed data; and support can assist by providing the relevant tables and columns the user would need to reference.

• While the Ticket Audit Logs are available in KQL, other log tables containing activity information cannot be queried through KQL such as:
  • swstaffactivitylog
  • swstaffloginlog
  • swstaffloginlog
• Data such as the different types of tickets views/filters or other data such as settings won't be available through KQL but one can easily get the list from the DB. Below are a few tables containing data not available through the UI.
  • swticketfilters
  • swticketviews
  • swattachments
  • swbayeswords
• Lastly, the knowledge base data is not available through KQL but one can reference some of the tables below to gather further details on the articles.
  • swkbarticles
  • swkbarticlesdata
  • swkbarticlelinks
  • swkbcategories

Note that this isn't a comprehensive list of all tables that can be used for reports, but grants an overview of the sort of information available in the DB that might not be accessible through KQL.