Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
Stuff((Select ', ' + Cast(t2.MonitorModel As varchar(10)) From tblMonitor t2
Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') Monitors,
countMonitor.numberMonitors As [# Monitors],
Stuff((Select ', ' + Cast(t2.SerialNumber As varchar(10)) From tblMonitor t2
Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') SerialNumbers,
Stuff((Select ', ' + Cast(t2.MonitorManufacturer As varchar(10))
From tblMonitor t2
Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2,
'') MonitorManufacturer,
Stuff((Select ', ' + Cast(t2.ManufacturedDate As varchar(20))
From tblMonitor t2
Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') ManufacturedDate,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblMonitor t1 On tblAssets.AssetID = t1.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join (Select tblMonitor.AssetID,
Count(tblMonitor.MonitorID) As numberMonitors
From tblMonitor
Group By tblMonitor.AssetID) countMonitor On countMonitor.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName