Notification

Icon
Error

Stale Tickets

Posted: Tuesday, July 27, 2021 8:29:30 PM(UTC)
brodiemac-too

brodiemac-too

Member Original PosterPosts: 48
0
Like
This issue has been solved! Click here to view the solution
I would like to create a stale ticket report that shows all tickets that have had no update in 7 days or more. I'm basing it on the report of tickets worked on during the last 7 days. My report works but I get a lot of duplicates. What's your best suggestions for this kind of report? I would like to add the criteria that this exclude closed and ignored tickets. This is what I have:
Code:
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,
  htblhistorytypes.name As HistoryType,
  htblhistory.date As HistoryDate
From htblticket
  Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
  Inner Join htblticketstates On htblticketstates.ticketstateid =
      htblticket.ticketstateid
  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 htbltickettypes On htblticket.tickettypeid =
      htbltickettypes.tickettypeid
  Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
  Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
      htblusers2.userid
  Inner Join htblhistory On htblticket.ticketid = htblhistory.ticketid
  Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Where htblhistory.date < GetDate() - 7 And htblticket.spam <> 'True'
Order By HistoryDate Desc,
  htblticket.ticketid
brodiemac-too
#1brodiemac-too Member Original PosterPosts: 48  
posted: 7/28/2021 8:47:09 PM(UTC)
Tweaked it to this. Hope it helps someone who may need it:
Code:
Select Distinct 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
From htblticket
  Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
  Inner Join htblticketstates On htblticketstates.ticketstateid =
      htblticket.ticketstateid
  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 htbltickettypes On htblticket.tickettypeid =
      htbltickettypes.tickettypeid
  Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
  Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
      htblusers2.userid
  Inner Join htblhistory On htblticket.ticketid = htblhistory.ticketid
  Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Where htblticket.updated < GetDate() - 7 And htblticketstates.statename <>
  'closed' And htblticket.spam <> 'True'
Order By LastUpdated Desc,
  htblticket.ticketid

Active Discussions

Lansweeper History of relationship between pc and monitor
by  hfaddy  
Go to last post Go to first unread
Last post: Yesterday at 1:44:38 PM(UTC)
Lansweeper New Hire Login Request Report
by  Scott Davis   Go to last post Go to first unread
Last post: 9/16/2021 7:16:46 PM(UTC)
Lansweeper SCCM end of life
by  Esben.D  
Go to last post Go to first unread
Last post: 9/16/2021 2:56:23 PM(UTC)
Lansweeper Microsoft Patch Tuesday – September 2021
by  Esben.D  
Go to last post Go to first unread
Last post: 9/14/2021 8:27:06 PM(UTC)
Lansweeper Apple “FORCEDENTRY” Zero-Day Vulnerability
by  Esben.D   Go to last post Go to first unread
Last post: 9/14/2021 12:07:54 PM(UTC)
Lansweeper ALL laptops HP 340S G7 Notebook PC and Bios version
by  Carlos Montes  
Go to last post Go to first unread
Last post: 9/13/2021 3:17:19 PM(UTC)