Find assets which have a ticket related to them along with details about both the assets and the tickets. This report provides an overview of all assets which have a ticket related to them. See which assets in your environment have tickets and might be having issues. The report also shows the subject and type of the ticket so you can see what type and the exact issue the asset was or still is facing.
Assets with Related Tickets Query
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
