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


1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
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

View solution in original post

5 REPLIES 5
nagendra
Engaged Sweeper II
support please response...
nagendra
Engaged Sweeper II
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
Champion Sweeper II
I updated my original post with the motherboard serial number.
nagendra
Engaged Sweeper II
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
Champion Sweeper II
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