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: 488  
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 SSH - Keyboard Interactive Authentication
by  blackmoonwolf   Go to last post Go to first unread
Last post: Today at 5:12:41 AM(UTC)
Lansweeper Get My Documents Folder Size
by  cbraafhart  
Go to last post Go to first unread
Last post: Yesterday at 2:25:18 PM(UTC)
Lansweeper Lansweeper Errors - Licencing and scanning
by  dnkleaf   Go to last post Go to first unread
Last post: 11/27/2020 1:02:13 PM(UTC)
Lansweeper Search for Blank Fields
by  CS Caritas Socialis IT  
Go to last post Go to first unread
Last post: 11/27/2020 12:19:05 PM(UTC)
Lansweeper Uninstalled software still listed
by  Dennis Gewillig   Go to last post Go to first unread
Last post: 11/27/2020 9:19:19 AM(UTC)
Lansweeper Software deployment initiated by user
by  LS_enthusiast_4444   Go to last post Go to first unread
Last post: 11/26/2020 9:28:18 PM(UTC)
Lansweeper Help Desk not disabling for regular users?
by  FrankSc  
Go to last post Go to first unread
Last post: 11/26/2020 8:18:38 PM(UTC)