2 weeks ago
Has anyone created a custom help desk report to capture the number of tickets closed by each help desk agent for the week, even if the agent didn't pick up the ticket? I can add the column "Last Editor" in my closed ticket queue, so I can see who closed each ticket, but I can't figure out what table/field the "Last Editor" is in, so that I can build this into a report, so that I can put in a custom date range to see who closed how many tickets each week.
a week ago
I see. I missed the Last Editor in the columns for the helpdesk section.
I'm not sure if this is the correct fields that you're looking for, and I can only test it on my limited data as I'm the only one that closes tickets here, but I do have some that are autogenerated via incomming emails, so here goes.
Taking an all tickets view I''ve modified a starting report point to drill down into what I think is the correct table data, since there doesn't seem to be any "Last Editor" type fields anywhere as you've undoubtedly noticed. I'm posting the code and a picture of my understanding of the layout.
From the htblticket table, link to the htblhistory, then thru the agentid to the htblagents table, then using the userid of the agentid, pull the name from the htblusers1 table. Make sure the htblusers1 table doesn't link back to any other tables or it seems to pull the creator of the ticket.
This code shows the layout above. You'll have to adjust to fit your reports of course.
Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.lastuserreply As LastUserReply,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblusers.name,
htblpriorities.name As Priority,
htblsource.name As Source,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject,
htblhistory.agentid,
htblusers1.name As name1,
htblusers1.description
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 htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblnotes On htblticket.ticketid = htblnotes.ticketid
Inner Join htblhistory On htblticket.ticketid = htblhistory.ticketid
Inner Join htblagents On htblhistory.agentid = htblagents.agentid
Inner Join htblusers htblusers1 On htblagents.userid = htblusers1.userid
Where htblticket.ticketid = 25854 And htbltickettypes.typename = 'IT Support'
And htblsource.name Not Like 'API'
Order By htblticket.ticketid
Testing it on one ticket (filtered on my report by #25854) it returns 6 results. I'm sure the joins are wrong somehow but see if your report pulls the data you're looking for.
2 weeks ago
Thanks for your reply. Basically, your understanding of what I'm trying to do is correct, although Last Editor is already a standard ticket field, at least when you create a tab view of tickets on the Help Desk main page. I have a tab where I view Closed tickets, and I've added this column to my tab view, so when I select this, it shows the agent's name who was the last person to edit the ticket, which would presumably be the person who also closed it. So I just need to figure out what table and field this existing ticket field corresponds to so that I can add it to a report. I don't want to add an additional field that my help desk agents have to fill in before they can close the ticket. The system can already identify who closed the ticket, the agent shouldn't have to self-identify. That also leaves the possibility that they might accidentally select the wrong name from the Combo box and throw off the statistics.
2 weeks ago
Not sure if this is what you're looking for, but here's an attempt with the limited scope you've given above.
Assuming your adding a custom ticket field to the Ticket Content section, called Last Editor, and using a combobox for the editor names (or regular text if you wish to add the names manually), that data is kept in two tables, htblticketcustomfield and htblcustomfields.
Here's the test I built to research this question.
1. Added a custom ticket content called Last Editor, Color Combobox (because why not), added my name (Randy).
2. Added this custom field to my standard helpdesk ticket.
3. Created a test ticket and selected the Last Editor, closed the ticket.
4. Ran the below attached report to find the links. This report is modified from the stock Helpdesk: Tickets closed in the last 7 days. The only added part relates to the two tables. With this information you should be able to use the htblcustomfields name to filter out the Last Editor link with the htblticketcustomfield Data information.
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,
htblticket.subject As Subject,
Max(htblhistory.date) As Closure,
htblcustomfields.name,
htblcustomfields.labeltext,
htblticketcustomfield.data
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
Inner Join htblticketstates htblticketstates1 On
htblticketstates1.ticketstateid = htblhistory.ticketstateid
Inner Join htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
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 htblticketstates1.statename =
'Closed'
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,
htblticket.subject,
htblticketcustomfield.data,
htblcustomfields.name,
htblcustomfields.labeltext
Having Max(htblhistory.date) > GetDate() - 7
Order By Closure Desc,
htblticket.ticketid
Hope this helps and good luck.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now