Dual Monitor Report

[digitalexpl0it]

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

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

.

[BioFerS]

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

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

[EtienneHB]

Is it possible to replace the monitor model by the asset name of the Monitor ?

[kim_kaberg]

I have this error when trying to create this report:

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

Website Version: 6.0.100.75
SERVERNAME: 6.0.100.75

I have migrated the DB to SQL Express 2014:

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.

[Daniel_B]

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.)

[lizaoreo]

I'm getting the following error when I try to run this report, any idea what's causing it?

Error while saving report: "There was an error parsing the query. [ Token line number = 1,Token line offset = 208,Token in error = Select]"

[RCorbeil]

Changing

Cast(t2.ManufacturedDate As varchar(10))

to VarChar(11) appears to fix that.

[digitalexpl0it · Accepted solution]

RC62N wrote:
Changing

Cast(t2.ManufacturedDate As varchar(10))

to VarChar(11) appears to fix that.

I was going to post that. Thanks

Updated:

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

[stiwa]

the report cuts the year of manufactureddate: e.g. Jun 25 201, Jul 9 201

[javier]

great report, thanks for share it