Open Tickets with SLA Overtime Audit

Find All Tickets Which Have Surpassed Their SLA Time

Make sure that users get responses within your SLA time. The service level agreement indicates the maximum time before users should get a response, initial or subsequent replies. This report provides a list of tickets which are on SLA overtime, meaning that those tickets should have been replied to. This allows you to prioritize these tickets easier and make sure users get their replies in time.

Open tickets with SLA overtime

Click to Open - Open Tickets with SLA Overtime Query
Select Top 1000000 htblticket.ticketid,
  '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
  htblticket.date As CreationDate,
  htblticket.updated As LastUpdated,
  htbltickettypes.typename As Type,
  htblticketstates.statename As State,
  htblpriorities.name As Priority,
  htblsource.name As Source,
  htblusers.name As [User],
  htblusers1.name As AssignedAgent,
  htblusers2.name As UserLastNote,
  '../helpdesk/icons/' + htbltickettypes.icon As icon,
  htblticket.subject As Subject,
  htblticket.slaname As SLA,
  htblticket.slainitial As InitialResponseDateLimit,
  Case
    When htblticket.slainitial < InitialResponseTime.FirstPublicReply Then 'Yes'
    When InitialResponseTime.FirstPublicReply Is Null And
      htblticket.slainitial < GetDate() Then 'Yes'
    Else 'No'
  End As InitialResponseBreach
From htblticket
  Left Join (Select Top 1000000 htblnotes.ticketid,
        Min(htblnotes.date) As FirstPublicReply
      From htblnotes
        Inner Join htblagents On htblnotes.userid = htblagents.userid
      Where htblnotes.notetype = 1
      Group By htblnotes.ticketid) As InitialResponseTime On
    htblticket.ticketid = InitialResponseTime.ticketid
  Inner Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
  Inner Join htbltickettypes On htbltickettypes.tickettypeid =
    htblticket.tickettypeid
  Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
  Inner Join htblsource On htblsource.sourceid = htblticket.sourceid
  Inner Join htblusers On htblusers.userid = htblticket.fromuserid
  Left Join htblagents On htblagents.agentid = htblticket.agentid
  Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
  Inner Join htblusers htblusers2 On
    htblusers2.userid = htblticket.userid_lastnote
Where htblticketstates.statename = 'Open' And Case
    When htblticket.slainitial < InitialResponseTime.FirstPublicReply Then 'Yes'
    When InitialResponseTime.FirstPublicReply Is Null And
      htblticket.slainitial < GetDate() Then 'Yes'
    Else 'No'
  End = 'Yes' And htblticket.spam <> 'True'
Order By htblticket.ticketid

Audit and Take Action in 3 Easy Steps

Download-Install-Lansweeper

1. Download & Install Lansweeper

Save-and-Run-the-Report

3. Run the Audit & Take Action

Download Lansweeper to Run this Audit

Harness the Power of Reporting