Add "Error Type Count" to existing asset report

[tarmitage84]

I've inherited Lansweeper ownership at my site and have little SQL knowledge - taking tutorials, but felt the need to reach out on this one. My asset report is below - it contains everything I need, except for scanning errors. What I am looking to add here is a "Count Error Type" function that will create a column in which the number of errors per device will be returned. e.g. if AssetABC had RPC Unavailable and Ping Failed errors, it would return a 2. No errors would return a 0, or blank. This format would allow me to generate total error count as well as % of devices experiencing at least 1 error. From the tutorial, it looks like this can be done with the COUNT/GROUP BY function, but I've tried it a few times and can't seem to get it to work. Thank you for any help on this.




Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease, tblMacOSInfo.SystemVersion)
As OS,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.IPAddress,
tblComputersystem.Domainrole,
tblState.Statename As State,
tblAssets.Firstseen,
tblAssets.Lastseen,
Round((Cast((DateDiff(dd, tblAssets.Lastseen, GetDate())) As float) / 1),
2) As DaysMissing,
tblAssetCustom.PurchaseDate,
Round((Cast((DateDiff(mm, tblAssetCustom.PurchaseDate, GetDate())) As float) /
12), 2) As AgeInYears
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Order By tblAssets.AssetName