Showing results for 
Show  only  | Search instead for 
Did you mean: 
Engaged Sweeper

I would like to render a report which shows our 'Root cause' trends from Lansweeper for a given timeframe (30, 60 and 90 days). The root cause data is required to close all tickets and can be one to three fields depending on the issue. The values were created under 'Ticket custom fields and currently I am only able to list the 'Root cause category field and not any subsequent sub fields, i.e root cause: Printing, sub-fields: Drivers, Network printer, Office printer, Scan/copy, Toner. From the code listed, I'm not able to find or list the sub-fields values from Custom Field data. Any help would be greatly appreciated.

30 Day Report:

Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket, As CreationDate,
htblticket.updated As LastUpdated,
htblticketstates.statename As State, As Priority, As AssignedTech, As [User],
htbltickettypes.typename As Type, As RootCause,
htblticket.subject As Subject
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 htblusers htblusers2 On htblticket.userid_lastnote =
Inner Join htblticketcustomfield On htblticket.ticketid =
Where > GetDate() - 30 And htblticket.spam <> 'True' And
htblticketcustomfield.fieldid = '2'
Order By RootCause Desc

Champion Sweeper II

What I've done in the past to figure out what field to include is use "Select *"  for a specific one day, and then I'd look to see what fields it showed and then use that.   Not the most efficient way of course, but it worked for me.