cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
digitalexpl0it
Engaged Sweeper II
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
.
1 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

View solution in original post

11 REPLIES 11
BioFerS
Engaged Sweeper II
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
Engaged Sweeper II
Is it possible to replace the monitor model by the asset name of the Monitor ?
kim_kaberg
Engaged Sweeper II
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
Lansweeper Alumni
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
Engaged Sweeper II
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
Honored Sweeper II
Changing
Cast(t2.ManufacturedDate As varchar(10))
to VarChar(11) appears to fix that.
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
Champion Sweeper
the report cuts the year of manufactureddate: e.g. Jun 25 201, Jul 9 201
javier
Engaged Sweeper II
great report, thanks for share it