→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
lcrowther
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
JacobH
Champion Sweeper III
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

View solution in original post

3 REPLIES 3
JacobH
Champion Sweeper III
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
Engaged Sweeper
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
Champion Sweeper III
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