need help in generating report

[nagendra]

i need to generate a report with these fields


Hostname
MotherBoard Make
MotherBoard Model
System serial no
Processor
Processor Speed
Processor Core
Installed Memory
Graphics Card
Graphics Card Serial No
HDD 0
HDD 1
HDD 2
HDD 3
HDD 4
Monitor1 Model
Monitor1 Serial No
Monitor2 Model
Monitor2 Serial No
Monitor3 Model
Monitor3 Serial No
Operating System

[nagendra]

support please response...

[nagendra]

Hi Support.

i tried this same option in ver 6 now am getting error

There was an error parsing the query. [ Token line number = 1,Token line offset = 360,Token in error = Select ]

[MikeMc]

I updated my original post with the motherboard serial number.

[nagendra]

Thanks for the code .. its working but one small change. can you change system serial no to motherboard serial no

System serial no -- Motherboard Serial NO

Thanks for the wonderful support

[MikeMc · Accepted solution]

Here is a report to get you started but be aware there will be a quite a few duplicate and omitted rows due to devices potentially having multiple processors and video cards.

Select Distinct tblAssets.AssetID,
  tblAssets.AssetName,
  tblBaseBoard.Manufacturer As MBMake,
  tblBaseBoard.Product As MBModel,
  tblBaseBoard.Serialnumber As MBSerial,
  tblProcessor.Name As CPUName,
  Cast((tblProcessor.MaxClockSpeed / 1000) As DECIMAL(2,1)) As ClockSpeedGHz,
  tblProcessor.NumberOfCores,
  tblAssets.Memory,
  tblVideoController.Caption As VideoCard,
  (Select T1.Model From (Select tblFloppy.Model,
      Row_Number() Over (Order By tblFloppy.Name) As Rownumber
    From tblFloppy
    Where tblFloppy.AssetID = tblAssets.AssetID And tblFloppy.InterfaceType <>
      N'USB') T1 Where T1.Rownumber = 1) As HDD0,
  (Select T2.Model From (Select tblFloppy.Model,
      Row_Number() Over (Order By tblFloppy.Name) As Rownumber
    From tblFloppy
    Where tblFloppy.AssetID = tblAssets.AssetID And tblFloppy.InterfaceType <>
      N'USB') T2 Where T2.Rownumber = 2) As HDD1,
  (Select T3.Model From (Select tblFloppy.Model,
      Row_Number() Over (Order By tblFloppy.Name) As Rownumber
    From tblFloppy
    Where tblFloppy.AssetID = tblAssets.AssetID And tblFloppy.InterfaceType <>
      N'USB') T3 Where T3.Rownumber = 3) As HDD2,
  (Select T4.Model From (Select tblFloppy.Model,
      Row_Number() Over (Order By tblFloppy.Name) As Rownumber
    From tblFloppy
    Where tblFloppy.AssetID = tblAssets.AssetID And tblFloppy.InterfaceType <>
      N'USB') T4 Where T4.Rownumber = 4) As HDD3,
  (Select T5.Model From (Select tblFloppy.Model,
      Row_Number() Over (Order By tblFloppy.Name) As Rownumber
    From tblFloppy
    Where tblFloppy.AssetID = tblAssets.AssetID And tblFloppy.InterfaceType <>
      N'USB') T5 Where T5.Rownumber = 5) As HDD4,
  (Select T6.MonitorModel From (Select tblMonitor.MonitorModel,
      Row_Number() Over (Order By tblMonitor.MonitorID) As Rownumber
    From tblMonitor
    Where tblMonitor.AssetID = tblAssets.AssetID) T6
  Where T6.Rownumber = 1) As Monitor1Model,
  (Select T7.SerialNumber From (Select tblMonitor.SerialNumber,
      Row_Number() Over (Order By tblMonitor.MonitorID) As Rownumber
    From tblMonitor
    Where tblMonitor.AssetID = tblAssets.AssetID) T7
  Where T7.Rownumber = 1) As Monitor1SerialNumber,
  (Select T8.MonitorModel From (Select tblMonitor.MonitorModel,
      Row_Number() Over (Order By tblMonitor.MonitorID) As Rownumber
    From tblMonitor
    Where tblMonitor.AssetID = tblAssets.AssetID) T8
  Where T8.Rownumber = 2) As Monitor2Model,
  (Select T9.SerialNumber From (Select tblMonitor.SerialNumber,
      Row_Number() Over (Order By tblMonitor.MonitorID) As Rownumber
    From tblMonitor
    Where tblMonitor.AssetID = tblAssets.AssetID) T9
  Where T9.Rownumber = 2) As Monitor2SerialNumber,
  (Select T9.MonitorModel From (Select tblMonitor.MonitorModel,
      Row_Number() Over (Order By tblMonitor.MonitorID) As Rownumber
    From tblMonitor
    Where tblMonitor.AssetID = tblAssets.AssetID) T9
  Where T9.Rownumber = 3) As Monitor3Model,
  (Select T10.SerialNumber From (Select tblMonitor.SerialNumber,
      Row_Number() Over (Order By tblMonitor.MonitorID) As Rownumber
    From tblMonitor
    Where tblMonitor.AssetID = tblAssets.AssetID) T10
  Where T10.Rownumber = 3) As Monitor3SerialNumber,
  tsysOS.OSname,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblBaseBoard On tblBaseBoard.AssetID = tblAssets.AssetID
  Inner Join tblProcessor On tblProcessor.AssetID = tblAssets.AssetID
  Inner Join tblVideoController On tblVideoController.AssetID =
    tblAssets.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName