List of computers without a monitor

[lcrowther]

Some of our monitors do not appear to be discoverable by WMI, even looking in device manager I just get Generic PnP Monitor.
I have been trying to build a report of all Computers that do not have a Monitor as a child asset.

I have found this report that shows me all connected monitors but I sort of need to reverse it.

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.Lastseen,
tblAssets.Lasttried,
countMonitor.numberMonitors,
tblAssets.IPAddress,
t1.AssetID
Order By tblAssets.AssetName,
Monitors

I think I need to change the Stuff query to something like where countMonitor.numberMonitors = 0 but I just cant get the syntax right.

[JacobH]

Yep! Sorry about that - I do that a lot when I make stuff in SSMS versus the GUI - I forget to change the column names, SSMS allows it but LS does not. I kinda wrote it on the fly when posting.

again - apologies.

[lcrowther]

I am afraid i get an error

Column names in each view or function must be unique. Column name 'AssetID' in view or function 'web50repassetassetrelations' is specified more than once.

So I changed
Select Top 1000000 tblAssets.AssetID,
to
Select Top 1000000 tblAssets.AssetID AS AssedID1,

and that resolved the issue.

Thank you for your help.

[JacobH · Accepted solution]

When you're looking for stuff that doesn't have something, or 'missing', you can run an 'Is Null' Query... which, generally, consists of a LEFT JOIN from your original table with all the devices or information you start with (this case its tblassets)... and then do a WHERE the joined table doesn't have a record (or IS NULL).


This should work for showing what does and does not have a monitor:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblMonitor.*
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblMonitor On tblAssets.AssetID = tblMonitor.AssetID
Where tblAssetCustom.State = 1 and tsysassettypes.assettypename = 'Windows'
Order By tblAssets.AssetName

Then, you apply the IS NULL portion to show only missing (i.e. where there is no record in the monitors table tblMonitor)... and I went ahead and threw in where tblassets.lastseen IS NOT NULL - because if it *is* null, that means it wasn't able to successfully scan the machine anyways - so it wouldn't know if it had a monitor or not.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblMonitor.*
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblMonitor On tblAssets.AssetID = tblMonitor.AssetID
Where tblAssetCustom.State = 1 and tsysassettypes.assettypename = 'Windows' and tblMonitor.AssetID is NULL and tblassets.lastseen is NOT NULL
Order By tblAssets.AssetName

Now this is *generally* accurate, unless there are WMI scanning errors on the MONITOR WMI query. I need to look into adding that as a check - but that's generally not seen very often.


https://www.tutorialspoint.com/sql/sql-left-joins.htm