Notification

Icon
Error

Connected monitors

Posted: Monday, December 29, 2014 12:57:49 PM(UTC)
Daniel.B

Daniel.B

Member Original PosterPosts: 1,150
3
Like
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


Code:

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
Reen87
#1Reen87 Member Posts: 8  
posted: 9/18/2015 10:19:16 AM(UTC)
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.
Daniel.B
#2Daniel.B Member Original PosterPosts: 1,150  
posted: 9/22/2015 1:41:03 PM(UTC)
You can do this with a filter criterion. The following report lists computers having less or more than 2 connected monitors.
Code:

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
norbertogomes
#3norbertogomes Member Posts: 3  
posted: 2/16/2016 6:53:42 PM(UTC)
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
#4Susan.A Member Administration Posts: 1,536  
posted: 2/21/2016 2:07:03 PM(UTC)
Originally Posted by: norbertogomes Go to Quoted Post
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.

Active Discussions

Lansweeper Monitor history showing only last monitors
by  cross_eur   Go to last post Go to first unread
Last post: 7/23/2021 6:06:51 PM(UTC)
Lansweeper Report Login time reduces number of computers by 300
by  cross_eur  
Go to last post Go to first unread
Last post: 7/23/2021 6:05:42 PM(UTC)
Lansweeper Merging 2 reports
by  Apaulcolypse   Go to last post Go to first unread
Last post: 7/22/2021 10:02:59 PM(UTC)
Lansweeper HELP - Add Registry Key Values to Asset Report
by  Apaulcolypse  
Go to last post Go to first unread
Last post: 7/22/2021 9:26:43 PM(UTC)
Lansweeper Can I request a custom report here?
by  Brian G   Go to last post Go to first unread
Last post: 7/22/2021 7:20:56 PM(UTC)
Lansweeper List all users with E-mail address
by  Brandon  
Go to last post Go to first unread
Last post: 7/21/2021 7:06:36 PM(UTC)
Lansweeper Identifying users of Windows legacy authentication
by  Baronet   Go to last post Go to first unread
Last post: 7/21/2021 5:26:38 PM(UTC)
Lansweeper Windows Version different between reports
by  RC62N  
Go to last post Go to first unread
Last post: 7/21/2021 3:27:04 PM(UTC)