Notification

Icon
Error

Helpdesk report for tickets for all on one reason - But different sub reasons.

Posted: Monday, February 22, 2021 7:22:56 PM(UTC)
TimHolmes1973

TimHolmes1973

Member Original PosterPosts: 34
0
Like
This issue has been solved! Click here to view the solution
I have set the HD up this way and need to run the report from this site.
https://www.lansweeper.c...category.aspx#post55047
But I want to add each ticket reason beneath, not just one, so I thought this would work but it did not, how can I add the next category.
Reason for ticket>
Hardware Related
Software Related
ETC.
I can get it if just the hardware related is there, but when I try to add software related, it shows no results.
Any advice please.

Select Distinct Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date,
htbltickettypes.typename,
htblticketstates.statename,
htblticket.subject,
htblusers1.name As [User Name],
htblusers.name As [Help Desk Agent Name],
Reasonforticket.data As [Reason for ticket],
HardwareRelated.data As [Hardware Related],
SoftwareRelated.data As [Software Related]
From htblticket
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers On htblusers.userid = htblagents.userid
Inner Join htblusers htblusers1 On htblusers1.userid = htblticket.fromuserid
Left Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'Reason For Ticket') As Reasonforticket
On htblticket.ticketid = Reasonforticket.ticketid
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'Hardware Related') As HardwareRelated On
htblticket.ticketid = HardwareRelated.ticketid
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'Software Related') As SoftwareRelated On
htblticket.ticketid = SoftwareRelated.ticketid
TimHolmes1973
#1TimHolmes1973 Member Original PosterPosts: 34  
posted: 4/5/2021 4:43:36 PM(UTC)
I had a reply from Lansweeper.
Select Top 1000000
RFT.data As [External Reference ID],
count(RFT.data) as Count
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
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
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
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%Reason for ticket%') As RFT
On RFT.ticketid = htblticket.ticketid
Where htblticket.spam <> 'True' And htbltickettypes.typename =
'IT Support' and htblticket.date > getdate()-30
group by RFT.data

This does it for the last 30 days, can anyone show me how to do it for this year please, split into months, as I want to be able to compare them.
TimHolmes1973
#2TimHolmes1973 Member Original PosterPosts: 34  
posted: 4/5/2021 5:29:05 PM(UTC)
Ok, so I have mashed this together and made this report, I am able to compare the values from this month to last and so on. If anyone has any better suggestions, please let me know.


Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
RFT.data As [Reason For Ticket, IT Support],
Count(RFT.data) As Count
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
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
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
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%Reason for ticket%') As RFT On
RFT.ticketid = htblticket.ticketid
Where htbltickettypes.typename = 'IT Support' And htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date),
RFT.data
Order By Year Desc,
Month Desc

Active Discussions

Lansweeper USB printers with toner levels
by  vstevo   Go to last post Go to first unread
Last post: Today at 3:15:35 PM(UTC)
Lansweeper Changing picture in Assets, New Location error.
by  jmani  
Go to last post Go to first unread
Last post: Today at 2:44:27 PM(UTC)
Lansweeper Showing all assets without a department
by  Andy.S   Go to last post Go to first unread
Last post: Yesterday at 8:30:43 AM(UTC)
Lansweeper Patch Volume Across Fleet
by  darren.kimber  
Go to last post Go to first unread
Last post: Yesterday at 8:29:33 AM(UTC)
Lansweeper Count by Department
by  Brandon   Go to last post Go to first unread
Last post: 4/21/2021 7:42:00 PM(UTC)
Lansweeper Adding computer type to Windows 10 report
by  Brandon   Go to last post Go to first unread
Last post: 4/19/2021 6:38:45 PM(UTC)
Report Center Hardware Inventory
by  Cori  
Go to last post Go to first unread
Last post: 4/16/2021 4:05:02 PM(UTC)