Report field for ticket closed date

[mhowell_ica]

Is there a field in htblticket or other table that tells when the ticket was closed?

[Nick_VDB · Accepted solution]

The htblticket.updated field will hold the datetime information of the last time a modification/note was made to a ticket. When a ticket gets closed an internal note is added automatically to the ticket, this will update the htblticket.updated field with the time when the ticket was closed.

Please use the report below for the information you are after. Instructions for adding this report to your Lansweeper installation can be found here. 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.
  
• 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. We also recommend updating to version 6.0.0.42 as the dictionary has been updated with all the helpdesk tables and fields.
  

Select Top 1000000 htblticket.ticketid As ID,
  htblticket.subject As Subject,
  htblticket.date As [Creation Date],
  htblpriorities.name As Priority,
  htblticketstates.statename As Status,
  htbltickettypes.typename As Type,
  htblticket.slaname As SLA,
  htblsource.name As Source,
  htblusers.name As [User],
  htblusers1.name As Agent,
  htblusers2.name As [User Last Reply],
  htblsource.icon As icon,
  htblticket.updated As [Close Date],
  htblticket.slainitial As [Initial Response Date Limit],
  Case When Cast(htblticket.slaresolved As nvarchar) Like '%1900%' Then Null
    Else htblticket.slaresolved End As [Resolve Date Limit]
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
Where htblticketstates.statename = 'Closed' And DatePart(mm, htblticket.date) =
  DatePart(mm, GetDate()) And DatePart(yy, htblticket.date) = DatePart(yy,
  GetDate())
Order By ID