Find Computers or Server with Helpdesk Requests
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