Notification

Icon
Error

Report to show all tickets that aren't assigned?

Posted: Friday, May 3, 2019 6:51:56 PM(UTC)
pryan67

pryan67

Member Original PosterPosts: 90
0
Like
I'm trying to find a report that will show me a list of all tickets that have been opened but not "picked up" yet.

Any suggestions? I've tried this with no luck:


Select Top 1000000 htblticket.ticketid As [Ticket Number],
htblticket.date As [Date Opened],
htblticket.updated As [Last Update],
htblticket.slaresolved As [Date Closed / Resolved],
htbltickettypes.typename As [Ticket Category],
htblticketstates.statename As [Ticket State],
tblAssets.AssetName As Asset,
htblticket.subject As Subject,
htblusers.name As [User],
htblusers.name As Agent
From htblticket
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Inner Join htblagents On htblagents.agentid = htblticket.agentid
Inner Join htblusers htblusers1 On htblagents.userid = htblusers1.userid
Inner Join tblAssets On htblticket.assetid = tblAssets.AssetID
Where htblticket.updated > DateAdd(day, DateDiff(day, 0, GetDate()), 0) And
htblusers.name Is Null
Order By [Last Update] Desc,
[Date Opened] Desc,
[Ticket State],
[Ticket Category]
pryan67
#1pryan67 Member Original PosterPosts: 90  
posted: 5/3/2019 8:55:09 PM(UTC)
So this is interesting. I modified it a little bit, but it's missing a significant number of tickets and I can't find any rhyme or reason to which are missing. It should be showing ALL tickets, from day one, yet I'm showing only 23 (there should be around 100...we just implemented it recently)


Select Top 1000000 htblticket.ticketid As [Ticket Number],
htblticket.date As [Date Opened],
htblticket.updated As [Last Update],
htblticket.slaresolved As [Date Closed / Resolved],
htbltickettypes.typename As [Ticket Category],
htblticketstates.statename As [Ticket State],
tblAssets.AssetName As Asset,
htblticket.subject As Subject,
htblusers.name As [User],
htblusers1.name As Agent
From htblticket
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Inner Join htblagents On htblagents.agentid = htblticket.agentid
Inner Join htblusers htblusers1 On htblagents.userid = htblusers1.userid
Inner Join tblAssets On htblticket.assetid = tblAssets.AssetID
Order By [Last Update] Desc,
[Date Opened] Desc,
[Ticket State],
[Ticket Category]
Mike B
#2Mike B Member Posts: 1  
posted: 12/16/2020 5:29:22 PM(UTC)
I know this is an old post but wanted to show what worked for me.

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
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
Where htblusers1.name Is Null And htblticket.spam <> 'True'
Group By htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar),
htblticket.date,
htblticket.updated,
htbltickettypes.typename,
htblticketstates.statename,
htblpriorities.name,
htblsource.name,
htblusers.name,
htblusers1.name,
htblusers2.name,
'../helpdesk/icons/' + htbltickettypes.icon,
htblticket.subject
Order By htblticket.ticketid
pryan67
#3pryan67 Member Original PosterPosts: 90  
posted: 12/16/2020 6:46:58 PM(UTC)
Thanks...that actually works very well. I ended up excluding closed and resolved tickets, but that worked for me.

Active Discussions

Lansweeper Rest API for Scanning
by  Brian Smith   Go to last post Go to first unread
Last post: Today at 7:35:27 PM(UTC)
Lansweeper Language translation not working anymore
by  Florian  
Go to last post Go to first unread
Last post: Today at 6:03:19 PM(UTC)
Lansweeper Use current user for scanning credentials?
by  pryan67   Go to last post Go to first unread
Last post: Today at 6:01:33 PM(UTC)
Lansweeper Bluetooth Info
by  FrankSc  
Go to last post Go to first unread
Last post: Today at 5:42:59 PM(UTC)
Lansweeper Question about Scanning Assets and Password Encryption
by  FrankSc   Go to last post Go to first unread
Last post: Today at 5:23:05 PM(UTC)
Lansweeper How to enable HTTPS in ver 6.0.150.60
by  Duncan.Miles  
Go to last post Go to first unread
Last post: Today at 1:11:19 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: Yesterday at 3:22:29 PM(UTC)
Lansweeper MS Edge Chromium LanSweeper Extension development
by  Slim D  
Go to last post Go to first unread
Last post: 1/15/2021 11:39:29 AM(UTC)