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

.

[digitalexpl0it]

Updated, this has more output, enjoy

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

[digitalexpl0it]

I got it figured out, thanks Lan sweeper support

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