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 Computer Aging Report with Year and Month using Purchase Date
by  wyl sg   Go to last post Go to first unread
Last post: Yesterday at 5:19:32 AM(UTC)
Report Center Default Browser and Version
by  RC62N  
Go to last post Go to first unread
Last post: 11/26/2020 8:00:54 PM(UTC)
Lansweeper Trying to make a custom report that sorts assets by state
by  mzipperer   Go to last post Go to first unread
Last post: 11/25/2020 5:36:52 PM(UTC)
Lansweeper Report - All Assets with Specfic Default Gateway
by  RC62N  
Go to last post Go to first unread
Last post: 11/25/2020 4:18:08 PM(UTC)
Report Center Windows update report
by  ToeJoe   Go to last post Go to first unread
Last post: 11/24/2020 3:39:00 PM(UTC)
Lansweeper Report - assets
by  Andy.S  
Go to last post Go to first unread
Last post: 11/23/2020 2:42:01 PM(UTC)
Lansweeper Run Report on PCs from a list
by  RC62N   Go to last post Go to first unread
Last post: 11/16/2020 4:23:12 PM(UTC)