Notification

Icon
Error

Dual Monitor Report - Need a report to show dual monitors on one row not two

Posted: Thursday, December 11, 2014 1:25:36 AM(UTC)
digitalexpl0it

digitalexpl0it

Member Original PosterPosts: 4
0
Like
This issue has been solved! Click here to view the solution
Hello,

I am trying to get a report done to show all the monitors we have, but if the computer asset has 2-3 monitors I need them displaying on the same line/row and not have multiple rows show of the same computer asset. I would also like a column with a monitor count.

I found a query online but it was for lansweeper 4 and not version 5. So some of the tables are missing. Help would be much appreciated

Code:

Select Top 1000000 tblAssets.AssetName,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblMonitor.MonitorModel As [Monitor 1 Model],
  tblMonitor.MonitorManufacturer As [Monitor 1 Vendor],
  tblMonitor.SerialNumber As [Monitor 1 Serial],
  tblMonitor1.MonitorModel As [Monitor 2 Model],
  tblMonitor1.MonitorManufacturer As [Monitor 2 Vendor],
  tblMonitor1.SerialNumber As [Monitor 2 Serial],
  TsysLastscan.Lasttime As [Monitor Last Scanned]
From tblAssets
  Left Join (Select tblAssets.AssetName,
    Min(tblMonitor.MonitorID) As Monitor1ID
  From tblMonitor
  Group By tblAssets.AssetName) Monitor1 On Monitor1.Computername =
    tblAssets.AssetName
  Left Join tblMonitor On tblMonitor.Computername = Monitor1.Computername And
    tblMonitor.MonitorID = Monitor1.Monitor1ID
  Inner Join TsysLastscan On tblAssets.AssetName = TsysLastscan.Computername
  Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
  Left Join (Select tblMonitor.Computername,
    Count(tblMonitor.MonitorID) As [Monitor Count]
  From tblMonitor
  Group By tblMonitor.Computername
  Having Count(tblMonitor.MonitorID) = 2) MonitorCount
    On MonitorCount.Computername = tblAssets.AssetName
  Left Join (Select tblMonitor.Computername,
    Max(tblMonitor.MonitorID) As Monitor2ID
  From tblMonitor
  Group By tblMonitor.Computername) Monitor2 On Monitor2.Computername =
    MonitorCount.Computername
  Left Join tblMonitor tblMonitor1 On tblMonitor1.Computername =
    Monitor2.Computername And tblMonitor1.MonitorID = Monitor2.Monitor2ID
Where tblAssets.Lastseen <> '' And TsysWaittime.CFGname = 'monitor'
Order By tblAssets.Domain,
  tblAssets.AssetName
.
digitalexpl0it
#1digitalexpl0it Member Original PosterPosts: 4  
posted: 12/11/2014 4:58:08 PM(UTC)
I got it figured out, thanks Lan sweeper support


Code:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Stuff((Select ', ' + Cast(t2.MonitorModel As varchar(10)) 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
  Inner 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 countMonitor.numberMonitors > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

digitalexpl0it
#2digitalexpl0it Member Original PosterPosts: 4  
posted: 12/11/2014 5:16:26 PM(UTC)
Updated, this has more output, enjoy

Code:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Stuff((Select ', ' + Cast(t2.MonitorModel As varchar(10)) From tblMonitor t2
  Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') Monitors,
  countMonitor.numberMonitors As [Number monitors],
  Stuff((Select ', ' + Cast(t2.SerialNumber As varchar(10)) From tblMonitor t2
  Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') SerialNumbers,
  Stuff((Select ', ' + Cast(t2.MonitorManufacturer As varchar(10)) From tblMonitor t2
  Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') MonitorManufacturer,
  Stuff((Select ', ' + Cast(t2.ManufacturedDate As varchar(10)) From tblMonitor t2
  Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') ManufacturedDate,
  tblAssets.Username
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner 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 countMonitor.numberMonitors > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName
javier
#3javier Member Posts: 5  
posted: 12/15/2014 11:27:21 AM(UTC)
great report, thanks for share it
stiwa
#4stiwa Member Posts: 64  
posted: 12/16/2014 8:20:03 AM(UTC)
the report cuts the year of manufactureddate: e.g. Jun 25 201, Jul 9 201
RC62N
#5RC62N Member Posts: 488  
posted: 12/16/2014 5:43:44 PM(UTC)
Changing
Code:
Cast(t2.ManufacturedDate As varchar(10))
to VarChar(11) appears to fix that.
digitalexpl0it
#6digitalexpl0it Member Original PosterPosts: 4  
posted: 12/16/2014 9:24:28 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
Changing
Code:
Cast(t2.ManufacturedDate As varchar(10))
to VarChar(11) appears to fix that.


I was going to post that. Thanks

Updated:

Code:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Stuff((Select ', ' + Cast(t2.MonitorModel As varchar(10)) From tblMonitor t2
  Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') Monitors,
  countMonitor.numberMonitors As [Number monitors],
  Stuff((Select ', ' + Cast(t2.SerialNumber As varchar(10)) From tblMonitor t2
  Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') SerialNumbers,
  Stuff((Select ', ' + Cast(t2.MonitorManufacturer As varchar(10))
  From tblMonitor t2
  Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2,
  '') MonitorManufacturer,
  Stuff((Select ', ' + Cast(t2.ManufacturedDate As varchar(20))
  From tblMonitor t2
  Where t1.AssetID = t2.AssetID For Xml Path('')), 1, 2, '') ManufacturedDate,
  tblAssets.Username,
  tsysIPLocations.IPLocation
From tblAssets
  Inner Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner 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 countMonitor.numberMonitors > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

lizaoreo
#7lizaoreo Member Posts: 12  
posted: 1/22/2015 7:54:38 PM(UTC)
I'm getting the following error when I try to run this report, any idea what's causing it?

Quote:

Error while saving report: "There was an error parsing the query. [ Token line number = 1,Token line offset = 208,Token in error = Select]"
Daniel.B
#8Daniel.B Member Posts: 1,150  
posted: 1/23/2015 8:52:18 AM(UTC)
These reports contain a function which isn't supported by SQL server Compact Edition.

In order to make these reports work, you need to have your Lansweeper database installed on SQL server Express or higher edition. (If you would like to migrate it, have a look at this article for reference.)
kim.kaberg@vr.se
#9kim.kaberg@vr.se Member Posts: 5  
posted: 10/17/2017 11:24:26 AM(UTC)
I have this error when trying to create this report:

Quote:
Invalid SELECT statement. Unexpected token "  Where" at line 9, pos 1.: Unexpected token "  Where" at line 9, column 1


I have recently updated Lansweeper to 6.0.100.75

Quote:
Website Version: 6.0.100.75
SERVERNAME: 6.0.100.75


I have migrated the DB to SQL Express 2014:

Quote:
SERVERNAME\SQLEXPRESS
Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


This error and similar shows up often when I try to create report from the Report Center.

EtienneHB
#10EtienneHB Member Posts: 7  
posted: 10/18/2017 3:10:40 PM(UTC)
Is it possible to replace the monitor model by the asset name of the Monitor ?
BioFerS
#11BioFerS Member Posts: 3  
posted: 11/15/2018 9:59:06 PM(UTC)
I think that I start copy and paste with this report. At the end I have this.
A 'Case' to change every not lucky name manufacturer from Lansweeper to his real name, if you have holes maybe you have to made a new 'When'.
The Size is meticulous hand work, all info is in the model then 'Case' and 'When' again.
To made cleanup and haven't monitors like the LCD of a notebook, I apply in every 'From' a 'Where' and there I match whatever name or code that isn't a external display.

I made with SQL Express to change from Compact version SQL Express

Quote:
Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tsysOS.OSname,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
countMonitor.numberMonitors As [Number monitors],
Case
When tblMonitor.MonitorManufacturer = 'ACR' Then 'Acer'
When tblMonitor.MonitorManufacturer = 'ACI' Then 'Asus'
When tblMonitor.MonitorManufacturer = 'BNQ' Then 'BenQ'
When tblMonitor.MonitorManufacturer = 'HWP' Then 'HP'
When tblMonitor.MonitorManufacturer = 'LEN' Then 'Lenovo'
When tblMonitor.MonitorManufacturer = 'LGD' Then 'LG'
When tblMonitor.MonitorManufacturer = 'LPL' Then 'LG'
When tblMonitor.MonitorManufacturer = 'GSM' Then 'LG'
When tblMonitor.MonitorManufacturer = 'PHL' Then 'Philips'
When tblMonitor.MonitorManufacturer = 'SEC' Then 'Samsung'
When tblMonitor.MonitorManufacturer = 'IVM' Then 'Iiyama'
When tblMonitor.MonitorManufacturer = 'MAX' Then 'Maxdata'
When tblMonitor.MonitorManufacturer = 'SNY' Then 'Sony'
When tblMonitor.MonitorManufacturer = 'SYN' Then 'Samsung'
When tblMonitor.MonitorManufacturer = 'ENC' Then 'EIZO'
When tblMonitor.MonitorManufacturer = 'NEC' Then 'NEC Technologies'
When tblMonitor.MonitorManufacturer = 'FUS' Then 'Fujitsu Siemens'
When tblMonitor.MonitorManufacturer Like '%FUJ%' Then 'Fujitsu Siemens'
When tblMonitor.MonitorManufacturer Like '%DEL%' Then 'Dell'
When tblMonitor.MonitorManufacturer Like '%SAM%' Then 'Samsung'
When tblMonitor.MonitorManufacturer Like '%Lenovo%' Then 'Lenovo'
When tblMonitor.MonitorManufacturer Like 'AUO' Then 'AU Optronics'
When tblMonitor.MonitorManufacturer Like 'BenQ' Then 'BenQ'
Else ''

End As [Monitor Manufacturer 1],
tblMonitor.MonitorModel As [S/N 1],
Case
When tblMonitor.MonitorModel Like '%19%' Then '19"'
When tblMonitor.MonitorModel Like '%22%' Then '22"'
When tblMonitor.MonitorModel Like '%23%' Then '23"'
When tblMonitor.MonitorModel Like '%24%' Then '24"'
When tblMonitor.MonitorModel Like '%27%' Then '27"'
When tblMonitor.MonitorModel Like '%201%' Then '20"'
When tblMonitor.MonitorModel Like '%Sony TV%' Then '50"'
When tblMonitor.MonitorModel Like '%L768%' Then '19"'
Else ''

End As [Size 1],
tblMonitor.SerialNumber As [Serial Number 1],
Case
When tblMonitor1.MonitorManufacturer = 'ACR' Then 'Acer'
When tblMonitor1.MonitorManufacturer = 'ACI' Then 'Asus'
When tblMonitor1.MonitorManufacturer = 'BNQ' Then 'BenQ'
When tblMonitor1.MonitorManufacturer = 'HWP' Then 'HP'
When tblMonitor1.MonitorManufacturer = 'LEN' Then 'Lenovo'
When tblMonitor1.MonitorManufacturer = 'LGD' Then 'LG'
When tblMonitor1.MonitorManufacturer = 'LPL' Then 'LG'
When tblMonitor1.MonitorManufacturer = 'GSM' Then 'LG'
When tblMonitor1.MonitorManufacturer = 'PHL' Then 'Philips'
When tblMonitor1.MonitorManufacturer = 'SEC' Then 'Samsung'
When tblMonitor1.MonitorManufacturer = 'IVM' Then 'Iiyama'
When tblMonitor1.MonitorManufacturer = 'MAX' Then 'Maxdata'
When tblMonitor1.MonitorManufacturer = 'SNY' Then 'Sony'
When tblMonitor1.MonitorManufacturer = 'SYN' Then 'Samsung'
When tblMonitor1.MonitorManufacturer = 'ENC' Then 'EIZO'
When tblMonitor1.MonitorManufacturer = 'NEC' Then 'NEC Technologies'
When tblMonitor1.MonitorManufacturer = 'FUS' Then 'Fujitsu Siemens'
When tblMonitor1.MonitorManufacturer Like '%FUJ%' Then 'Fujitsu Siemens'
When tblMonitor1.MonitorManufacturer Like '%DEL%' Then 'Dell'
When tblMonitor1.MonitorManufacturer Like '%SAM%' Then 'Samsung'
When tblMonitor1.MonitorManufacturer Like '%Lenovo%' Then 'Lenovo'
When tblMonitor1.MonitorManufacturer Like 'AUO' Then 'AU Optronics'
When tblMonitor1.MonitorManufacturer Like 'BenQ' Then 'BenQ'
Else ''

End As [Monitor Manufacturer 2],
tblMonitor1.MonitorModel As [S/N 2],
Case
When tblMonitor1.MonitorModel Like '%19%' Then '19"'
When tblMonitor1.MonitorModel Like '%22%' Then '22"'
When tblMonitor1.MonitorModel Like '%23%' Then '23"'
When tblMonitor1.MonitorModel Like '%24%' Then '24"'
When tblMonitor1.MonitorModel Like '%27%' Then '27"'
When tblMonitor1.MonitorModel Like '%201%' Then '20"'
When tblMonitor1.MonitorModel Like '%Sony TV%' Then '50"'
When tblMonitor1.MonitorModel Like '%L768%' Then '19"'
Else ''

End As [Size 2],
tblMonitor1.SerialNumber As [Serial Number 2]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join (Select tblMonitor.AssetID,
Count(tblMonitor.MonitorID) As numberMonitors
From tblMonitor
Where tblMonitor.MonitorModel Not Like '%LCD 1366%' And
tblMonitor.MonitorModel Not Like '%Non-PnP%' And
tblMonitor.MonitorModel Not Like '%LCD 1600x900%' And
tblMonitor.MonitorModel Not Like '%LTN125AT01401%' And
tblMonitor.MonitorModel Not Like '%B125XW01 V0%' And
tblMonitor.MonitorManufacturer Not Like '%AUO%' And
tblMonitor.MonitorManufacturer Not Like '%LGD%'
Group By tblMonitor.AssetID) countMonitor On countMonitor.AssetID =
tblAssets.AssetID

Left Join (Select Top 10000 tblMonitor.AssetID,
Min(tblMonitor.MonitorID) As Monitor1ID
From tblMonitor
Where tblMonitor.MonitorModel Not Like '%LCD 1366%' And
tblMonitor.MonitorModel Not Like '%Non-PnP%' And
tblMonitor.MonitorModel Not Like '%LCD 1600x900%' And
tblMonitor.MonitorModel Not Like '%LTN125AT01401%' And
tblMonitor.MonitorModel Not Like '%B125XW01 V0%' And
tblMonitor.MonitorManufacturer Not Like '%AUO%' And
tblMonitor.MonitorManufacturer Not Like '%LGD%'
Group By tblMonitor.AssetID) As Monitor1 On tblAssets.AssetID =
Monitor1.AssetID

Left Join tblMonitor On Monitor1.Monitor1ID = tblMonitor.MonitorID
Left Join (Select Top 10000 tblMonitor.AssetID,
Max(tblMonitor.MonitorID) As Monitor2ID
From tblMonitor
Where tblMonitor.MonitorModel Not Like '%LCD 1366%' And
tblMonitor.MonitorModel Not Like '%Non-PnP%' And
tblMonitor.MonitorModel Not Like '%LCD 1600x900%' And
tblMonitor.MonitorModel Not Like '%LTN125AT01401%' And
tblMonitor.MonitorModel Not Like '%B125XW01 V0%' And
tblMonitor.MonitorManufacturer Not Like '%AUO%' And
tblMonitor.MonitorManufacturer Not Like '%LGD%'
Group By tblMonitor.AssetID) As Monitor2 On tblAssets.AssetID =
Monitor2.AssetID

Left Join tblMonitor tblMonitor1 On Monitor2.Monitor2ID =
tblMonitor1.MonitorID And Monitor2.Monitor2ID != Monitor1.Monitor1ID
Left Join (Select tblMonitor.AssetID,
Count(tblMonitor.MonitorID) As [Monitor Count]
From tblMonitor
Where tblMonitor.MonitorModel Not Like '%LCD 1366%' And
tblMonitor.MonitorModel Not Like '%Non-PnP%' And
tblMonitor.MonitorModel Not Like '%LCD 1600x900%' And
tblMonitor.MonitorModel Not Like '%LTN125AT01401%' And
tblMonitor.MonitorModel Not Like '%B125XW01 V0%' And
tblMonitor.MonitorManufacturer Not Like '%AUO%' And
tblMonitor.MonitorManufacturer Not Like '%LGD%'
Group By tblMonitor.AssetID
Having Count(tblMonitor.MonitorID) = 2) As MonitorCount On
tblAssets.AssetID = MonitorCount.AssetID

Order By tblAssets.AssetName

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)