→ 🚀What's New? Explore Lansweeper's Fall 2024 Updates! Fall Launch Blog !

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

I'm trying to modify the report Helpdesk: Time worked and create two reports both filtering the tickets by a custom field named "StonePanels Ticket" which is a radio button yes/no. I want it to only show tickets that have that field marked yes but can't seem to find what table to pull that from. 

One report I need to show the last year, and the other the last months tickets. Both should only be closed tickets. Any help pointing me in the right direction for the ticket custom field would be appreciated. 

2 REPLIES 2
Errhoss
Engaged Sweeper

Is there a way to filter tickets to the "last month" instead of x number of days?

Errhoss
Engaged Sweeper

Well I've made some progress but am just getting stuck adding the column to this report to show time worked.

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],
  htblusers1.name As AssignedAgent,
  htblticket.subject As Subject,
  Max(htblhistory.date) As Closure
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 htblhistory On htblticket.ticketid = htblhistory.ticketid
  Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
  Inner Join htblticketstates htblticketstates1 On
      htblticketstates1.ticketstateid = htblhistory.ticketstateid
  Inner Join htblticketcustomfield On htblticket.ticketid =
      htblticketcustomfield.ticketid
  Inner Join htblcustomfields On htblcustomfields.fieldid =
      htblticketcustomfield.fieldid
Where htblticketstates.statename = 'Closed' And htblticket.spam <> 'True' And
  htblhistorytypes.name In ('Status changed', 'Note added and state changed',
  'Internal note added and state changed') And htblticketstates1.statename =
  'Closed' And htblticketcustomfield.data = 'yes' And htblcustomfields.name =
  'StonePanels Ticket'
Group By htblticket.ticketid,
  '#' + Cast(htblticket.ticketid As nvarchar),
  htblticket.date,
  htblticket.updated,
  htbltickettypes.typename,
  htblticketstates.statename,
  htblpriorities.name,
  htblsource.name,
  htblusers.name,
  htblusers1.name,
  htblticket.subject,
  htblusers2.name
Having Max(htblhistory.date) > GetDate() - 30
Order By Closure Desc,
  htblticket.ticketid