cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JTempleton
Engaged Sweeper III
Good day!

I have modified the built-in Open Tickets with SLA overtime and removed a few fields. The report is only showing tickets that have their initial response time breached. How can I create a new report that will show tickets that have breached the more than just the initial SLA?

Select Top 1000000 '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
Convert(VarChar(10),htblticket.date,101) As CreationDate,
Convert(VarChar(10),htblticket.updated,101) 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 Case
When htblticket.slainitial < InitialResponseTime.FirstPublicReply Then 'Yes'
When InitialResponseTime.FirstPublicReply Is Null And
htblticket.slainitial < GetDate() Then 'Yes' Else 'No'
End = 'Yes' And (htblticketstates.statename = 'Open' And htblticket.spam <>
'True' Or htblticketstates.statename = 'New')
Order By htblticket.ticketid
1 REPLY 1
Esben_D
Lansweeper Employee
Lansweeper Employee
I've reversed some of the logic in the report you gave to create a report which shows if a ticket breached the Resolve SLA.

The report takes the date of the last public note of a ticket and compares it to the slaresolve date.

Select Top 1000000 '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
Convert(VarChar(10),htblticket.date,101) As CreationDate,
Convert(VarChar(10),htblticket.updated,101) 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.slaresolved As ResolvedDateLimit,
Case
When htblticket.slaresolved < ResponseTime.LastPublicReply Then 'Yes'
When ResponseTime.LastPublicReply Is Null And htblticket.slaresolved <
GetDate() Then 'Yes'
Else 'No'
End As ResponseBreach
From htblticket
Left Join (Select Top 1000000 htblnotes.ticketid,
Max(htblnotes.date) As LastPublicReply
From htblnotes
Inner Join htblagents On htblnotes.userid = htblagents.userid
Where htblnotes.notetype = 1
Group By htblnotes.ticketid) As ResponseTime On htblticket.ticketid =
ResponseTime.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 = 'Closed' And Case
When htblticket.slaresolved < ResponseTime.LastPublicReply Then 'Yes'
When ResponseTime.LastPublicReply Is Null And htblticket.slaresolved <
GetDate() Then 'Yes'
Else 'No'
End = 'Yes' And DatePart(yy, htblticket.slaresolved) <> 1900 And
htblticket.spam <> 'True'
Order By htblticket.ticketid

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now