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 Device Duplicates
by  Ian.Prentice   Go to last post Go to first unread
Last post: 7/23/2021 11:33:00 AM(UTC)
Lansweeper Anti-Virus on Mac
by  Ian.Prentice  
Go to last post Go to first unread
Last post: 7/23/2021 9:04:13 AM(UTC)
Lansweeper Computers Listed as Users
by  td1020   Go to last post Go to first unread
Last post: 7/22/2021 5:16:19 PM(UTC)
Lansweeper Can not see second server in error
by  Jürgen  
Go to last post Go to first unread
Last post: 7/22/2021 3:47:19 PM(UTC)
Lansweeper OU missing
by  Fred   Go to last post Go to first unread
Last post: 7/22/2021 2:08:05 PM(UTC)
Lansweeper Certificate status & end dates
by  VysJamesk  
Go to last post Go to first unread
Last post: 7/22/2021 1:07:02 PM(UTC)
Lansweeper NIST 800-171 Compliance
by  rader  
Go to last post Go to first unread
Last post: 7/20/2021 10:13:17 PM(UTC)