Notification

Icon
Error

Report with working time, closed state, ticket type against custom filed

Posted: Tuesday, April 13, 2021 12:47:30 AM(UTC)
Dimitar Staykov IT

Dimitar Staykov IT

Member Original PosterPosts: 3
0
Like
Hi I am tryning to build a report with specific parameteres. The most important are Working time and a custom field from htblticketcustomfield, named OfficeLocation. I was able to get the result but can it be optimised some how?


Code:
Select Top 1000000 htblticket.ticketid,
htblticket.ticketid As Ticket,
htblticket.date As 'Creation Date',
Max(htblhistory.date) As 'Close Date',
htblticketstates.statename As state,
htbltickettypes.typename As Type,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) %
60))) + ' minutes' As TimeWorked,
OfficeLocation.data As OfficeLocation
From htblticket
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'OfficeLocation') As OfficeLocation On
OfficeLocation.ticketid = htblticket.ticketid
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
Inner Join (Select Top 1000000 htblnotes.ticketid,
Sum(htblnotes.timeworked) As MinutesWorked
From htblnotes
Group By htblnotes.ticketid) WorkTime On WorkTime.ticketid =
htblticket.ticketid
Where htblticketstates.statename = 'Closed' And htbltickettypes.typename =
'EMERGENCY' And OfficeLocation.data LIKE '%Asia'
Group By htblticket.ticketid,
htblticket.date,
htblticketstates.statename,
htbltickettypes.typename,
htblsource.name,
htblusers.name,
htblusers1.name,
'../helpdesk/icons/' + htbltickettypes.icon,
htblticket.subject,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) %
60))) + ' minutes',
OfficeLocation.data,
'#' + Cast(htblticket.ticketid As nvarchar),
htblticket.updated,
htblusers2.name,
WorkTime.MinutesWorked
Having Max(htblhistory.date) > GetDate() - 121
Order By WorkTime.MinutesWorked Desc,
htblticket.ticketid



Also I would like to have some report with total working time per every OfficeLocation data. For Example:

I have three values for OfficeLocation custom filed: Europe, Asia, USA.
I would like in the report working time for last 120 days to be sumarised per OfficeLocation

For last 120 days
Europe - 06:40:00
USA - 12:04:00
Asia - 8:04:00
Dimitar Staykov IT
#1Dimitar Staykov IT Member Original PosterPosts: 3  
posted: 4/16/2021 2:03:53 PM(UTC)
I have contacted the Lansweeper support and the built this report code - works great

Select distinct Top 1000000 htblticketcustomfield.data,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,Custom1.MinutesWorked) %
60))) + ' minutes' As TimeWorked
From htblticketcustomfield
inner join htblcustomfields on htblticketcustomfield.fieldid = htblcustomfields.fieldid
inner join (Select Top 1000000 htblticketcustomfield.data,
Sum(htblnotes.timeworked) As MinutesWorked
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
Inner join htblnotes on htblnotes.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistory On htblticketcustomfield.ticketid = htblhistory.ticketid
Inner Join htblticketstates On htblticketstates.ticketstateid = htblhistory.ticketstateid
Inner join htblticket on htblticket.ticketid = htblticketcustomfield.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Where htblticketstates.statename = 'Closed' And htblticket.spam <> 'True' And
htblhistorytypes.name In ('Status changed', 'Note added and state changed',
'Internal note added and state changed') And htblticketstates.statename =
'Closed' and htblcustomfields.name Like '%OfficeLocation%'
group by htblticketcustomfield.data having max(htblhistory.date) > getdate() - 120 ) As Custom1 on Custom1.data = htblticketcustomfield.data

Active Discussions

Report Center Microsoft Outlook email bug EX255650
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 2:30:16 PM(UTC)
Lansweeper Patch Tuesday May 2021
by  Esben.D  
Go to last post Go to first unread
Last post: 5/11/2021 8:13:06 PM(UTC)
Lansweeper Report showing only Wi-Fi Devices and MAC addresses
by  Andy.S   Go to last post Go to first unread
Last post: 5/11/2021 2:23:24 PM(UTC)
Lansweeper Modifying Purchase Date / Yearly Refresh Report
by  Cripple.Zero   Go to last post Go to first unread
Last post: 5/7/2021 7:06:47 PM(UTC)
Lansweeper Tweak LanSweepers Bios Version Audit to show Last Seen
by  Tyler M.  
Go to last post Go to first unread
Last post: 5/6/2021 8:22:40 PM(UTC)
Lansweeper Patch Volume Across Fleet
by  darren.kimber  
Go to last post Go to first unread
Last post: 5/5/2021 11:29:33 PM(UTC)