Resolution Report

[mdavis2503]

Is there a report that can be generated to identify the resolution by problem? Such as printer, phone, workstation, etc?

[rader]

mdavis2503 wrote:
Is there a report that can be generated to identify the resolution by problem? Such as printer, phone, workstation, etc?

Try this. Starting with Helpdesk: All tickets, add htblnotes, checking the note (ntext) box, and adding this string to the criteria... Like '%printer%'

The Like '%printer%' will find all printer references in the search function. Replace that with phone, workstation or etc.

Adjust to your needs. YMMV.

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,
  htblusers1.name As AssignedAgent,
  htblusers2.name As UserLastNote,
  '../helpdesk/icons/' + htbltickettypes.icon As icon,
  htblticket.subject As Subject,
  htblticketstates.description,
  htblusers.name,
  htblnotes.note
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 lansweeperdb.dbo.htblnotes On htblticket.ticketid =
      htblnotes.ticketid And htblusers.userid = htblnotes.userid And
      htblusers1.userid = htblnotes.userid And htblusers2.userid =
      htblnotes.userid
Where htblnotes.note Like '%printer%'
Order By htblticket.ticketid

If you get automated emails to your helpdesk tickets, you will probably get results that include strings in your notes that start out as

<div style="font:12px Arial,Verdana;font-size:12px;font-family:Arial,Verdana;">...

and continue to the end, but the search "Like '%printer%'" will find that in the string and display it there. If you exclude this string of gibberish with

Where htblnotes.note Like '%printer%' AND NOT Like '<div style%'

then you greatly cleanup your display but may eliminate the search your looking for.

If anyone knows how to cleanup the gibberish string and still display the results, feel free to chime in.