Dual Monitors report

[poweld1]

Is it possible to have a report to show dual monitor information on one row formatted as below: -

Computer Name | Monitor 1 Model | Serial Number of Monitor 1 | Monitor 2 | Serial number of Monitor 2

The current report I found here displays the Monitor information on separate rows.

[Nick_VDB · Accepted solution]

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename,
  tsysOS.OSname,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblMonitor.MonitorModel As [Monitor 1 Model],
  tblMonitor.MonitorManufacturer As [Monitor 1 Manufacturer],
  tblMonitor.SerialNumber As [Monitor 1 Serial Number],
  tblMonitor1.MonitorModel As [Monitor 2 Model],
  tblMonitor1.MonitorManufacturer As [Monitor 2 Manufacturer],
  tblMonitor1.SerialNumber As [Monitor 2 Serial Number]
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
  Inner Join (Select Top 1000000 tblMonitor.AssetID,
    Min(tblMonitor.MonitorID) As Monitor1ID
  From tblMonitor
  Group By tblMonitor.AssetID) As Monitor1 On tblAssets.AssetID =
    Monitor1.AssetID
  Inner Join tblMonitor On Monitor1.Monitor1ID = tblMonitor.MonitorID
  Inner Join (Select Top 1000000 tblMonitor.AssetID,
    Max(tblMonitor.MonitorID) As Monitor2ID
  From tblMonitor
  Group By tblMonitor.AssetID) As Monitor2 On tblAssets.AssetID =
    Monitor2.AssetID
  Inner Join tblMonitor tblMonitor1 On Monitor2.Monitor2ID =
    tblMonitor1.MonitorID
  Inner Join (Select tblMonitor.AssetID,
    Count(tblMonitor.MonitorID) As [Monitor Count]
  From tblMonitor
  Group By tblMonitor.AssetID
  Having Count(tblMonitor.MonitorID) = 2) As MonitorCount On tblAssets.AssetID =
    MonitorCount.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Mister_Nobody]

Please convert report to LS 6 DB schema format.

[Hemoco]

You can do this using the report below, which supports 2 monitors. It lists each monitor in a separate column instead of a separate line.

Select Top 1000000 Web40OSName.Compimage As icon, tblComputers.Computername, tblComputers.Computer, tblComputers.Domain, Web40OSName.OSname As OS, Web40OSName.SP, Web40OSName.OScode As Code, 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 tblComputers Inner Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join (Select tblMonitor.Computername, Min(tblMonitor.MonitorID) As Monitor1ID From tblMonitor Group By tblMonitor.Computername) Monitor1 On Monitor1.Computername = tblComputers.Computername Left Join tblMonitor On tblMonitor.Computername = Monitor1.Computername And tblMonitor.MonitorID = Monitor1.Monitor1ID Inner Join TsysLastscan On tblComputers.Computername = 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 = tblComputers.Computername 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 tblComputers.Lastseen <> '' And TsysWaittime.CFGname = 'monitor' Order By tblComputers.Domain, tblComputers.Computer

To use the specified report, do the following:
• Open the Lansweeper configuration console and select Reports & Alerts\Report Builder. Hit the “New” button.
• Copy the SQL code provided and paste it at the bottom of the newly created report, replacing the default SQL code.
• Click somewhere near tblComputers so the new code applies.
• Give the report a “View name” and a “Report name” and hit the “Save” button.
• Double-click on the report in the report list to see its results and export options. The report will also be listed in the Lansweeper web console under "All available reports".

[cpmining]

That makes 3 of us. Would definitely appreciate the option to do that in one line rather than seperate lines.

[zulqarnaina]

hi poweld1.
any luck on the dula monitor report?.. im trying to get the same thing as well. would be nice to have