KQL - Troubleshooting Article

 

This article explains how to correctly troubleshoot KQL issues. There are several troubleshooting steps, detailed by the following flowchart (click to enlarge)

 

 

Each blue rectangle represents a troubleshooting procedure and links to a section in this article:

1. Install the reports app for instance
2. Fix the team permissions
3. Check the department permissions
4. Troubleshoot the report/query
5. Troubleshoot the KQL Error
6. Troubleshoot Installation
7. Troubleshoot Error Log/Trace
8. Try different functions
9. Optimize the KQL report
10. Try other export formats
11. Verify the date/time conditions in reports
12. Troubleshoot the permissions to report schedules
13. Troubleshoot the scheduled task for reports
14. Check outbound email logs
15. Identify the report from a database backup

---

 

Install the reports app for instance

To use KQL reports and their features, the Reports app must be installed for that instance. If for some reason, you’re unable to access the reports section in the staff CP, it's possible that the Reports app is not installed for the instance. Refer to the following screenshot:

1. Sign in to Admin CP and select Apps on the left sidebar
2. If the Reports app is listed as “Not installed”, click on it.
3. On the next screen, press the Install button

---

 

Fix the team permissions

We have verified that the Reports app is installed and you still don’t have the option to edit existing or create new reports. In this case, you need to check the permissions for the reports as suggested below.

1. Sign in to Admin CP
2. Navigate to Staff > Manage Teams and select your team
3. Then head over to the Permissions: Staff Tab and enable all the options (as seen in the screenshot below)
4. Following this, you should be able to access the reports area in the Staff CP. Also, you should be able to create new reports and edit the existing ones.
   
   
   
   

---

 

Check the department permissions

In some cases, you'd be able to run the report and will give you zero results for that report. Before we look into the other troubleshooting steps, ensure that you've permission to run reports on all the departments. 

1. Sign in to Admin CP
2. Navigate to Staff > Manage Staff 
3. Ensure you have got the right department assignments for which you’re to run the reports

Alternatively, you can try the following workaround:

1. Sign in to Admin CP
2. Navigate to Staff > Manage Teams and select your team
3. Then head over to the Permissions: Staff Tab and disable the option “Can only run reports on content staff have access to (through department permissions)”

---

 

Troubleshoot the report/query

Now that we have verified that staff has permission to run reports across all the departments, however, his reports are still generating zero results or the results are incorrect. In these cases, 

1. Always check the KQL query for completeness. 
2. You need to ensure that all the KQL fields and table names are enclosed correctly and conform to the KQL syntax. Although Kayako allows the usage of both punctuation marks and single quotes to enclose the entities fields, however, they can be used as mix and match. If some field is opened with a single quote, it must end with a single quote or vice versa when a punctuation mark is used.
3. Next, ensure that all the conditions in the WHERE clause of the report are logically correct and complete. This part is really important and explained with the following example:

SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' = 'General'

Under normal circumstances, this report must generate an output (result) as long as you have at least one ticket in the department ‘General’. However, the same report will fail to generate results if there’s an issue with the conditions used in the WHERE clause of the report. For instance, the name of the department is either misspelled or doesn’t exist. 

When complex reports are built using KQL and use a plethora of conditions to filter out the results, there could be small mistakes in the conditions. A mistake could be as small as a typo or it could be an issue with the logical reasoning used in the report. For example, you can’t run the same example report as follows:

SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' = 'General' AND 'Tickets.Department' = 'Sales'

Although this report looks good and KQL doesn't throw any errors for this report, it will still generate zero results along with an information message as seen below. 

The report failed because a ticket cannot belong to two departments at the same time. So the condition in the WHERE clause isn’t correct logically and can be corrected as follows:

SELECT 'Tickets.Ticket ID' FROM 'Tickets' WHERE 'Tickets.Department' IN ('General','Sales')

Complex reports can have multiple conditions and values in the WHERE clause, so make sure all the conditions are correct and placed logically.

Whenever in doubt, strip the original query to a really simple KQL report with just one condition and start adding the rest of the conditions one by one. This will help you find out which condition is causing the issue and makes it easier for you to debug the issue.

If you find that date/timestamps are not the same as seen in the Staff CP, this is not necessarily an issue. Kayako generates the timestamps and dates based on the date/time preferences used in an instance. You can find these date/time preferences under:

• Admin CP > Settings > Date and Time (Note: These settings apply system-wide)
• Staff CP > Home > My Preferences > Time Zone (Note: These are user-specific preferences)

---

 

Troubleshoot the KQL Error

While running a report you may run into different types of errors. The most common of these is a KQL error, which is generated automatically to handle some known issues with the reports and aided with visual hints. You can easily locate these visual cues to identify the error and solve the root cause. 

As soon as you run some report which has invalid input, the KQL report engine will throw an error which can be seen in the top right corner. These errors are very easy to follow and can be resolved straight away. Following are the most common errors:

• X is not related Y - In these errors, the field/dataset X is not related to the database table Y. For example, refer to the screenshot shared above wherein, we’re trying to fetch ticket ID from the chats data source which happens to be incorrect. To fix these issues, you must correct the source used in the FROM part of the report.
  
  
  
  
• Unknown or unsupported function {{function_name}} at offset {{index}}: While KQL reports support almost all the MySQL functions in the report editor, some of them are reserved. If you are trying to use such a function in the report, the error is intended and you must get rid of that function to run the report. For example, refer to the following screenshot:
  
  
  
  
• Expected field, value, or expression at offset X near Y: This is another error generated by Kayako when your report is not complete and missing maybe a closing parenthesis, a value, or expression. Another reason for this error is the hidden whitespaces in the reports. So make sure to export the KQL query to a text editor and clean up all the trailing whitespace.
  
  

---

 

Troubleshoot Installation

In some rare cases, the KQL report engine may run out of memory or resources while running a report. In those cases, Kayako would either throw an error or a warning message. Instead, it’d appear that report keeps on loading forever. This often happens when the results aren’t filtered and you try to export the dataset as a whole. For example, the following query may bring down the entire system which has more than 10K tickets.

SELECT 'Ticket Posts.Contents' FROM 'Ticket Posts'

To verify this cause, update the report with the following WHERE clause:

SELECT 'Ticket Posts.Contents' FROM 'Ticket Posts' LIMIT 1

The updated report will force the KQL engine to return just the first row instead of all the data rows matching that query. If this works fine, we suggest you optimize the KQL report.

If the report fails yet again and it’s not due to the errors discussed above, make sure to check the network tab of the browser developer tools. This one is really helpful when the error originates on the server. If this is true, you’ll notice that

• The request timed out 
• There’s an internal server error

In both cases, there must be troubleshooting at the installation level. Please submit a ticket for a Support Agent to help you.

---

 

Troubleshoot Error Log trace

If, you've run into a product error like an uncaught error as shown above. For these errors, we need to ensure:

1. The installation meets minimum server requirements
2. The instance is running the most recent version of Kayako

If both conditions are met, please submit a ticket.

---

 

Try different functions

You may notice that report data is not correct, especially the dates/time stamps generated in the reports. In these cases, please submit a ticket for a Support Agent to help you.

 

---

 

Optimize the KQL report

If you’re reading this chapter, it certainly means you have the export option enabled for all the formats, and for some reason, it’s still not working. In these cases, we always start by optimizing the report (especially for XLS and XLSX formats).

---

 

Try other export formats

When working with the exports, if you notice that one format of the export is not working, always try the alternative formats to see if it makes any difference. 

When very large reports are exported in the XLS/XLSX formats, the system tends to crash due to the limited resources. For the same reason, we have added an option in the Admin CP to disable the Excel exports. You can find it under Admin CP > Settings: Server settings and performance.

Kayako Download Classic version uses PHPExcel for these two formats and it depends on the following resources:

• memory_limit
• Max_execution_time

It is possible to upregulate these two parameters for your Kayako installation. 

Finally, if you notice that the export has failed for the CSV/HTML format, you should investigate:

• Error Logs in Kayako (can be found under Admin CP > Logs > Error Logs)
• PHP Error Logs (location depends on the type of server)
• Server Error Logs 

If you need support for the topics above, please submit a ticket.

---

 

Verify the date/time conditions in reports

When starting to use the reports, it is common to find that the data in the scheduled report is from the wrong date/time range. When working with the scheduled reports, it's worth noting that:

• Execution and timing of scheduled reports depend entirely on a scheduled task for reports.
• The date/time conditions used in the report to filter report data for a time range. 
• The date/time preferences of the server where the instance is hosted

For example, you can set up multiple reports to run at the end of every month and uses the date-range operator ThisMonth(). We know this operator only returns the data for the current month. So if a report schedule is delayed in execution at the end of the month, there's a chance that the schedule may run in the next month. This delay in the execution can change the results completely. Take into account the date/time preferences used on the server and admin CP, and this gets even more complex.

So to work around this limitation, please schedule such reports in the next month and use the function LastMonth(). This workaround can also be used for other operators such as LastWeek(), Yesterday(). 

---

 

Troubleshoot the permissions to report schedules

Alright! You’ve created a report and are ready to schedule it to run at a regular interval but the schedule option is missing. In this case, you need to check the schedule report permissions are enabled for the team in question.

1. Sign in to Admin CP
2. Navigate to Staff > Manage Teams and select your team
3. Then head over to the Permissions: Staff Tab and enable all the options next to “Scheduled Reports” (as shown in the screenshot below)
4. Following this, you should be able to schedule reports in Kayako.
   
   
   
   

---

 

Troubleshoot the scheduled task for reports

If you have set up a report schedule and it doesn't work, we always start from the report to see if the task has been executed or not, and proceed as follows:

1. Sign in to Kayako and go to the affected report
2. Once the report opens, go to its schedules tab and it has the following three pieces of information available:
   • The number of schedules still available/due or that are still active on the report.
   • The next run date of the schedule based on the frequency selected for the report schedule i.e. daily, weekly, monthly, etc.
   • The last run date of the schedule (refer to the screenshot below)
     
     
     
     
3. Based on the information we have under the schedules tab, it's easy to identify if the schedule was ever processed by Kayako. It's worth noting whenever a recurring schedule like daily, weekly, or monthly is executed/completed by Kayako, it always updates the next run and last run date.
   For the report schedules that are scheduled to run once, it's removed from the list of schedules as soon it's completed
4. If you find that the next run and last run date have been updated in the list of schedules, skip to the next chapter and troubleshoot the outbound emails.
5. If you find that the next run and last run date are still not updated in the list of schedules, we need to check the scheduled task responsible for this. You can check this from:
   1. Admin CP > Scheduled Tasks > Manage. 
   2. Ensure that the scheduled task "Run scheduled reports" is enabled
6. Now that we have established that the task is enabled, we need to check the task logs from Admin CP > Scheduled Tasks >Task Log and search for "reportemailing". We need to ensure that the reportemailing task ran around the next run date in the schedule. If you're unable to find any logs, this probably means, the report was scheduled outside the business hours of the organization. To handle this situation, you can automate the task "Run scheduled reports" and set up this cron on the server.
7. If you've found that log exists in the task log around/after the next run date of the schedule, access the scheduled task URL manually - which means you need to copy the scheduled task URL and open it in a new tab. This way you'll be running the task manually and if there's an error on execution, troubleshoot the error.
8. If there's no error on the execution of the task manually, request the database backup or the output of the following SQL query:
                                                  SELECT * FROM swreportschedules
9. In the output of the last query, we search for our schedule using the reportid and look for the value in the column 'isexecuted'. If this value is set to false (0), we are certain that the schedule was never executed and we need to delete and recreate it. This could happen when some data is malformed while writing the entry into the database.
10. Otherwise, if the value of 'isexecuted' set to true (1), this means either the task was interrupted and we need to carry out the following actions
    • optimize the report to ensure it's not exhausting resources while executing the schedule.
    • Change the export format CSV and recreate and test the schedule again.
11. If you notice the same results after changing the format and still the scheduled report is not delivered, please submit a ticket.

---

 

Check outbound email logs

If you're reading this chapter, it probably means that the schedule's next run date and last run date have been updated in the report schedules. However, you didn't receive the email from that report schedule. In this case, we need to check the logs for the outgoing emails as explained below.

• For all the users using a custom SMTP mailer: in this case, you will have to dig through the outbound email logs at their end since the SMTP server is their own. 

In either case, if it's confirmed that the outgoing emails are not triggered for the report schedule, try the following:

1. Create a test report with a limited number of rows, say 10
2. Schedule the report to run once and run the report schedule task manually from Admin CP > Scheduled Task > Manage
3. If the schedule is executed and removed from the report after the last step, please submit a ticket.

---

 

Identify the report from a database backup

If you're unable to find and delete a specific report schedule, this could happen when the report schedule is created by the user who's deleted from the system. To edit/remove such report schedules, proceed as follows:

1. Identify the report in Kayako from the emails delivered to your mailbox by report schedule. You can use the subject of the email and the content of the export to identify the report.
2. Once the report is identified, note down its reportid - this is the index at the end of the direct URL for that report. Refer to the following screenshot.
   
   
   
   
3. Next, request a database backup of the instance or run the following query on the database:
                                                SELECT * FROM swreportschedules
4. From the output of the last query, you'll have a list of all the report schedules in your instance. 
5. Identify the schedule linked to the reportid noted in step #2. Copy its recipients from the column 'ccemails', so you can build again or update the recipients of the change.
6. Run the following query  to delete the schedule from the database:
                                  DELETE FROM swreportschedules WHERE reportid = xxx