Connected monitors

[Daniel_B]

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

[norbertogomes]

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 ]

[Susan_A]

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]

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]

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.