Creating common SLA reports with KQL

Service level agreements (SLAs) and escalations both play a significant role in the support industry. It is one of the ways to ensure you are providing excellent customer service in a timely manner.

Using GFI HelpDesk's reporting query language (KQL), you can build reports that gives you an insight into your SLA and escalations metrics.

In this topic, you can learn how to build a few commonly requested reports.

To create the report, follow the steps mentioned below:

1. Log into the staff control panel and click Reports in the top navigation bar.
2. Click the New Report link that appears below it.
3. In the New Report pop-up that appears, enter a descriptive title for your report in the Title field.
4. From the Primary Source dropdown select Tickets.
5. Click Next to create your blank report.
6. On the next page, you can see a text area with the word SELECT in it. Here you can enter the KQL query that can pull your report.



Enter the KQL query here

7. Based on the report you want to create, copy the query code, and paste it into the text area on your report page.
• Overdue tickets
  SELECT 'Tickets.Ticket Mask ID', 'Tickets.Status', IF(('Tickets.Reply Due Date' <= DateNow()) AND ('Tickets.Reply Due Date' != ''), 'Overdue', 'Not Overdue') as 'OVERDUE Status' FROM 'Tickets' WHERE 'Tickets.Creation Date' = ThisMonth() AND 'Tickets.Is Resolved' = '0'
• Escalated tickets, by department
  SELECT SUM(IF(('Tickets.Is Escalated' = '1') ,1,0)) AS Escalated , SUM(IF(('Tickets.Is Escalated' = '0') ,1,0)) AS 'Non-Escalated' FROM 'Tickets' WHERE 'Tickets.Creation Date' = ThisMonth() GROUP BY 'Tickets.Department'
  Note:

  You can also set up an SLA based on Ticket Priority or Ticket Status by specifying it in the criteria. This helps in automatically changing the SLA plan when a ticket is moved from one priority to another or from one status to another.

• Average first response time for tickets, overall
  SELECT AVG('Tickets.First Response Time') FROM 'Tickets' WHERE 'Tickets.Creation Date' = ThisMonth() AND 'Tickets.First Response Time' > '0'
  Note:

  Default reports are available for an average of first response time. You can find these by navigating to Staff Control Panel > Reports > Manage Reports > then search with First Response. All related reports are listed.

• Average response time for tickets created today
  SELECT AVG('Ticket Posts.Response Time') FROM 'Ticket Posts' WHERE 'Ticket Posts.Creation Date' = today() AND 'Ticket Posts.Creator' = 'Staff' AND 'Ticket Posts.Response Time' > '0' Group by 'Ticket Posts.Full Name'
• Breakdown of tickets that have been waiting on a reply for 24 hours, 24-48 hours, and more than 72 hours
  SELECT Sum(IF('Tickets.Last User Reply' <= '86400', 1,0)) AS 'Within 24 hours', Sum(IF('Tickets.Last User Reply' > '86400' AND 'Tickets.Last User Reply' <= '172800', 1,0)) AS ' 24-48 hours', Sum(IF('Tickets.Last User Reply' > '259200', 1,0)) As 'More than 72 hours' FROM 'Tickets' WHERE 'Tickets.Status' = 'Open' AND 'Tickets.Creation Date'>= Date_Sub(NOW(), INTERVAL 5 Day)
• Average response time for ticket posts created during working hours
  SELECT AVG('Ticket Posts.SLA Response Time') FROM 'Ticket Posts' WHERE 'Ticket Posts.Creation Date' = ThisMonth() AND 'Ticket Posts.Creator' = 'Staff' AND 'Ticket Posts.Response Time' != '0' GROUP BY Y('Ticket Posts.Full Name')
8. Click Run Report to see your results.