Notification

Icon
Error

External Monitor Report

Posted: Friday, September 18, 2020 3:38:05 PM(UTC)
ethant

ethant

Member Original PosterPosts: 1
0
Like
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.c...port/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
RC62N
#1RC62N Member Posts: 478  
posted: 9/18/2020 4:49:03 PM(UTC)
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.
Code:
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.
Code:
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.
Code:
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.

Active Discussions

Lansweeper License renewal - but why
by  mrusso   Go to last post Go to first unread
Last post: Yesterday at 5:01:47 PM(UTC)
Lansweeper Deployment Package Error Message
by  Brandon  
Go to last post Go to first unread
Last post: Yesterday at 2:04:25 PM(UTC)
Lansweeper Asset Type Mail Server
by  MarkPayton   Go to last post Go to first unread
Last post: Yesterday at 1:03:54 PM(UTC)
Lansweeper Upgrade Win 10 build to version 2004
by  Jean-FB  
Go to last post Go to first unread
Last post: 10/28/2020 7:34:29 PM(UTC)
Lansweeper Uptime only shows Standby
by  Gst4r   Go to last post Go to first unread
Last post: 10/28/2020 4:19:33 PM(UTC)
Lansweeper Excepciones
by  Pablo  
Go to last post Go to first unread
Last post: 10/27/2020 7:35:21 PM(UTC)
Lansweeper Help desk API
by  Skylar@Hennig   Go to last post Go to first unread
Last post: 10/27/2020 5:01:18 PM(UTC)
Lansweeper Helpdesk API
by  Skylar@Hennig  
Go to last post Go to first unread
Last post: 10/27/2020 4:44:50 PM(UTC)