External Monitor Report

[Ethant]

Apologies for reposting, but it seems more fitting to place it here....

I am using the built in report in Lansweeper to see which users have which monitors.
After some validation, i noticed that laptops that do not have external monitors are not listed.
Can someone help me alter the below SQL to include laptops that do not have an external monitor listed?

I got the SQL from:
https://www.lansweeper.com/report/connected-monitors/

SQL Code below:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried,
Stuff((Select ', ' + CAST(t2.MonitorModel As Varchar(100)) from
tblMonitor t2 Where t1.AssetID=t2.AssetID for XML path('')),1,2,'') Monitors,
countMonitor.numberMonitors As [Number monitors]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblMonitor t1 On tblAssets.AssetID = t1.AssetID
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
Group by tblAssets.AssetID, tblAssets.AssetName, tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10, tblAssets.IPAddress, tblAssets.Lastseen,
tblAssets.Lasttried, countMonitor.numberMonitors, t1.AssetID
Order By tblAssets.AssetName,
Monitors

[RCorbeil]

The INNER JOIN against the list of monitors limits your results to those machines that have external monitors. If you want to list all machines, regardless of whether they have an external monitor, change it to a LEFT JOIN.

Left Join (Select
             tblMonitor.AssetID,
             Count(tblMonitor.MonitorID) As numberMonitors
           From
             tblMonitor
           Group By
             tblMonitor.AssetID) countMonitor On countMonitor.AssetID = tblAssets.AssetID

LEFT JOINing opens things up too broadly. Since you haven't any filters in place limiting your results to computers, you'll get too many results. Assuming you're just after Windows machines, add an asset type filter to your WHERE clause.

Where tblAssetCustom.State = 1
  AND tblAssets.Assettype = -1   -- alternatively, AND tsysAssetTypes.AssetTypename = 'Windows'

If your inventory has more than Windows machines, you'll need to adjust the asset type filter accordingly.

If you don't want to produce a NULL for machines with no monitors, you may want to adjust the Number Monitors column to deal with that.

CASE
  WHEN countMonitor.numberMonitors IS NULL
  THEN 0
  ELSE countMonitor.numberMonitors
END As [Number monitors]

If you want to filter out servers, you'll need to add a little more to the query. Refer to tblComputerSystem.DomainRole and, if you want descriptions, tblDomainRoles.DomainRoleName.