Helpdesk: Assets with related tickets (Built-in)

[Nick_VDB]

Added in v.6.0.100

The report below lists assets that have been added to a ticket in the helpdesk. General information about the asset and the ticket it is related to are also given back in the report.

The report will only list users that meet all of the following criteria:

• The asset has been added to a ticket in the helpdesk.
  
• The ticket has not been set to 'Ignore'.
  

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  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,
  htblusers2.name As UserLastNote,
  htblticket.subject As Subject
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 htblticketasset On htblticket.ticketid = htblticketasset.ticketid
  Inner Join tblAssets On tblAssets.AssetID = htblticketasset.assetid
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where htblticket.spam <> 'True'
Order By tblAssets.IPNumeric,
  tblAssets.Domain,
  tblAssets.AssetName,
  htblticket.ticketid