Helpdesk Notes stored in SQL Tables for search and reporting

[jkot]

Helpdesk Notes stored in SQL Tables for search and reporting.

Need better ways to search and report on the content of the ticket notes. If the text of the notes was actually in a table instead of files on the server, this would help.

[Esben_D]

This is already possible currently. The contents of helpdesk notes can be found in the table htblnotes. Additionally in version 6.0.100.12, 32 built-in helpdesk reports were added to Lansweeper, so be sure to update to the latest version by following this knowledgebase article: https://www.lansweeper.com/kb/118/updating-your-installation.html

Here is an example of a modified version of one of the built-in reports which shows all agent notes in the past 7 days which have the word 'Ticket' in them.

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,
  '../helpdesk/icons/' + htbltickettypes.icon As icon,
  htblticket.subject As Subject,
  htblnotes.note As Content,
  htblnotes.date As NoteSentDate,
  Case htblnotes.notetype When 1 Then 'Public Reply' When 2 Then 'Internal Note'
    Else 'Initial Ticket' End As NoteType,
  htblusers2.name As NoteSentBy
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 htblnotes On htblticket.ticketid = htblnotes.ticketid
  Inner Join htblagents htblagents1 On htblagents1.userid = htblnotes.userid
  Inner Join htblusers htblusers2 On htblusers2.userid = htblagents1.userid
Where htblnotes.note Like '%Ticket%' And htblnotes.date > GetDate() - 7 And
  htblticket.spam <> 'True'
Order By NoteSentDate Desc,
  htblticket.ticketid