cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
poweld1
Champion Sweeper
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.
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
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

View solution in original post

5 REPLIES 5
Nick_VDB
Champion Sweeper III
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
Honored Sweeper II
Please convert report to LS 6 DB schema format.
Hemoco
Lansweeper Alumni
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
Engaged Sweeper
That makes 3 of us. Would definitely appreciate the option to do that in one line rather than seperate lines.
zulqarnaina
Engaged Sweeper
hi poweld1.
any luck on the dula monitor report?.. im trying to get the same thing as well. would be nice to have