Showing results for 
Show  only  | Search instead for 
Did you mean: 
Engaged Sweeper III
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.
Lansweeper Employee
Lansweeper Employee
This is already possible currently. The contents of helpdesk notes can be found in the table htblnotes. Additionally in version, 32 built-in helpdesk reports were added to Lansweeper, so be sure to update to the latest version by following this knowledgebase article:

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, As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State, As Priority, As Source, As [User], As AssignedAgent,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject,
htblnotes.note As Content, As NoteSentDate,
Case htblnotes.notetype When 1 Then 'Public Reply' When 2 Then 'Internal Note'
Else 'Initial Ticket' End As NoteType, As NoteSentBy
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.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 =
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 > GetDate() - 7 And
htblticket.spam <> 'True'
Order By NoteSentDate Desc,