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

[DavidPK]

We've created a sample report. Instructions for adding this report to your Lansweeper installation can be found here: https://www.lansweeper.com/knowledgebase/how-to-add-a-report-to-your-lansweeper-installation/

If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial: https://www.w3schools.com/sql/
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here: https://www.lansweeper.com/knowledgebase/accessing-the-lansweeper-database-documentation/
• Checking out our report library: https://www.lansweeper.com/report/

Select Top 1000000
  t.ticketid,
  '#' + Cast(t.ticketid As nvarchar) As Ticket,
  t.date As CreationDate,
  t.updated As LastUpdated,
  tt.typename As Type,
  ts.statename As State,
  p.name As Priority,
  s.name As Source,
  u.name As [User],
  u.department As Department,
  WorkTime.MinutesWorked,
  t.subject As Subject,
  '../helpdesk/icons/' + tt.icon As icon,
  t.fromuserid,
  cfAcc.data As [Accessories],
  cfConfigCat.data As [Configuration Category],
  cfRoot1.data As [Root Cause 1],
  cfRoot2.data As [Root Cause 2]

From htblticket t
Inner Join htblpriorities p On p.priority = t.priority
Inner Join htblticketstates ts On ts.ticketstateid = t.ticketstateid
Inner Join htblusers u On u.userid = t.fromuserid
Left Join htblagents a On a.agentid = t.agentid
Left Join htblusers u1 On u1.userid = a.userid
Inner Join htbltickettypes tt On tt.tickettypeid = t.tickettypeid
Inner Join htblsource s On s.sourceid = t.sourceid
Inner Join htblusers u2 On t.userid_lastnote = u2.userid
Inner Join (Select htblnotes.ticketid, Sum(htblnotes.timeworked) As MinutesWorked
            From htblnotes Group By htblnotes.ticketid) WorkTime
        On WorkTime.ticketid = t.ticketid
-- Custom field joins
Left Join (Select htblticketcustomfield.ticketid, htblticketcustomfield.data
           From htblticketcustomfield
           Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
           Where htblcustomfields.name = 'Accessories') cfAcc
     On t.ticketid = cfAcc.ticketid
Left Join (Select htblticketcustomfield.ticketid, htblticketcustomfield.data
           From htblticketcustomfield
           Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
           Where htblcustomfields.name = 'Configuration Category') cfConfigCat
     On t.ticketid = cfConfigCat.ticketid
Left Join (Select htblticketcustomfield.ticketid, htblticketcustomfield.data
           From htblticketcustomfield
           Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
           Where htblcustomfields.name = 'Root Cause 1') cfRoot1
     On t.ticketid = cfRoot1.ticketid
Left Join (Select htblticketcustomfield.ticketid, htblticketcustomfield.data
           From htblticketcustomfield
           Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
           Where htblcustomfields.name = 'Root Cause 2') cfRoot2
     On t.ticketid = cfRoot2.ticketid

Order By WorkTime.MinutesWorked Desc, t.ticketid