→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Daniel_B
Lansweeper Alumni
List of computers with connected monitors to them (1 row per computer)
Note: This report can't be used on SQL compact

Meets all the following criteria:
- Active asset
- Windows asset
- one or more monitors connected

Sorted on:
- Asset name
- Monitor model name



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
4 REPLIES 4
norbertogomes
Engaged Sweeper
I tried to use this report but when I put this query, the message below appears:

There was an error parsing the query. [ Token line number = 1,Token line offset = 178,Token in error = Select ]
norbertogomes wrote:
I tried to use this report but when I put this query, the message below appears:

There was an error parsing the query. [ Token line number = 1,Token line offset = 178,Token in error = Select ]

Daniel's report uses the Stuff function, which is only supported by SQL Server. Most likely your database is hosted in SQL Compact, which doesn't support this function. This is a SQL Compact limitation, not a Lansweeper limitation. You can verify which database server you're using under Configuration\Database Scripts.

There is a built-in monitor report in the Reports tab that is compatible with SQL Compact, "Monitor: information". It lists each monitor as a separate line.
Daniel_B
Lansweeper Alumni
You can do this with a filter criterion. The following report lists computers having less or more than 2 connected monitors.

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
Having countMonitor.numberMonitors <> 2
Order By tblAssets.AssetName,
Monitors
Reen87
Engaged Sweeper II
Hi,

Is it posilbe to get this report to show me what computer (with user info) that has anything difrent that 2 monitors conected?.

I need to make a list off everyone that has 1, 3 and 4 screens on there computer.