Community FAQ
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
darrenh
Engaged Sweeper II

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

1 REPLY 1
DavidPK
Lansweeper Tech Support
Lansweeper Tech Support

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:

 

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

 

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now