updated helpdesk time work report-report custom field for root cause

[darrenh]

I had a ticket open ages ago and lansweeper support indicated that the department was based on the last update user, not the user the ticket was assigned to.

I eventually dove into the SQL and this is fairly easy to change.

SQL for time worked, including department the actual user belongs to, and in total minutes not days hours minutes to make it much easier for excel exports to do auto sum.

I am looking for assistance on adding 2 more columns, the hblticketcustomfield data I want one column with things such as accessories, configuration category, etc. and then a second column with then the detail from those various ticket resolution root causes, there's 2 levels of root cause.

These are prebuilt in lansweeper but no idea what the range or ID's are to get the output?
Thoughts?

Here's the SQL with proper department and minutes reporting in the meantime:

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],
htblusers.department As Department,
WorkTime.MinutesWorked,
htblticket.subject As Subject,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.fromuserid
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 (Select Top 1000000 htblnotes.ticketid,
Sum(htblnotes.timeworked) As MinutesWorked
From htblnotes
Group By htblnotes.ticketid) WorkTime On WorkTime.ticketid =
htblticket.ticketid
Order By WorkTime.MinutesWorked Desc,
htblticket.ticketid