Hardware Inventory

[Mister_Nobody]

I decided to publish some of my reports.

Problem: collect HW inventory.
Solution: LS HW Inventory Report.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblBaseBoard.Manufacturer As MBManufacturer,
  tblBaseBoard.Product,
  tblAssets.Memory,
  tblVideoController.Caption As VideoCard,
  tblProcessor.Name,
  tblProcessor.Caption,
  tblNetworkAdapter.Manufacturer As NetworkManufacturer,
  tblNetworkAdapter.Name As NetworkAdapter,
  tblFloppy.Model As HDDModel,
  tblFloppy.Size
From tblAssets
  Inner Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Inner Join tblVideoController
    On tblAssets.AssetID = tblVideoController.AssetID
  Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where tblVideoController.Caption Not Like '%ware%' And
  tblVideoController.Caption Not Like '%ConfigMgr%' And
  tblVideoController.Caption Not Like '%VGA%' And tblVideoController.Caption Not
  Like '%Hyper-V%' And tblNetworkAdapter.Name Not Like '%virtual%' And
  tblNetworkAdapter.Name Not Like '%VPN%' And tblFloppy.InterfaceType <> 'USB'
Order By tblAssets.AssetName

[cori406]

Thank you Brandon this is perfect!

[cori406]

Mister Nobody wrote:
I decided to publish some of my reports.

Problem: collect HW inventory.
Solution: LS HW Inventory Report.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblBaseBoard.Manufacturer As MBManufacturer,
  tblBaseBoard.Product,
  tblAssets.Memory,
  tblVideoController.Caption As VideoCard,
  tblProcessor.Name,
  tblProcessor.Caption,
  tblNetworkAdapter.Manufacturer As NetworkManufacturer,
  tblNetworkAdapter.Name As NetworkAdapter,
  tblFloppy.Model As HDDModel,
  tblFloppy.Size
From tblAssets
  Inner Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Inner Join tblVideoController
    On tblAssets.AssetID = tblVideoController.AssetID
  Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where tblVideoController.Caption Not Like '%ware%' And
  tblVideoController.Caption Not Like '%ConfigMgr%' And
  tblVideoController.Caption Not Like '%VGA%' And tblVideoController.Caption Not
  Like '%Hyper-V%' And tblNetworkAdapter.Name Not Like '%virtual%' And
  tblNetworkAdapter.Name Not Like '%VPN%' And tblFloppy.InterfaceType <> 'USB'
Order By tblAssets.AssetName

- Just a question since I am not very good at creating reports yet, is it possible to add the HDD size to this report?
Thanks by the way, this is a very helpful report.

[brandon_jones]

Try this:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblBaseBoard.Manufacturer As MBManufacturer,
tblBaseBoard.Product,
tblAssets.Memory,
tblVideoController.Caption As VideoCard,
tblProcessor.Name,
tblProcessor.Caption,
tblNetworkAdapter.Manufacturer As NetworkManufacturer,
tblNetworkAdapter.Name As NetworkAdapter,
tblFloppy.Model As HDDModel,
tblFloppy.Size,
tblDiskdrives.Caption As Drive,
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) As [%SpaceLeft],
tblDiskdrives.Lastchanged As LastChanged
From tblAssets
Inner Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblVideoController On
tblAssets.AssetID = tblVideoController.AssetID
Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner Join lansweeperdb.dbo.tblDiskdrives On tblAssets.AssetID =
tblDiskdrives.AssetID
Where tblVideoController.Caption Not Like '%ware%' And
tblVideoController.Caption Not Like '%ConfigMgr%' And
tblVideoController.Caption Not Like '%VGA%' And tblVideoController.Caption Not
Like '%Hyper-V%' And tblNetworkAdapter.Name Not Like '%virtual%' And
tblNetworkAdapter.Name Not Like '%VPN%' And tblFloppy.InterfaceType <> 'USB'
Order By tblAssets.AssetName

Cori wrote:

Mister Nobody wrote:
I decided to publish some of my reports.

Problem: collect HW inventory.
Solution: LS HW Inventory Report.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblBaseBoard.Manufacturer As MBManufacturer,
  tblBaseBoard.Product,
  tblAssets.Memory,
  tblVideoController.Caption As VideoCard,
  tblProcessor.Name,
  tblProcessor.Caption,
  tblNetworkAdapter.Manufacturer As NetworkManufacturer,
  tblNetworkAdapter.Name As NetworkAdapter,
  tblFloppy.Model As HDDModel,
  tblFloppy.Size
From tblAssets
  Inner Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
  Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Inner Join tblVideoController
    On tblAssets.AssetID = tblVideoController.AssetID
  Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
  Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Where tblVideoController.Caption Not Like '%ware%' And
  tblVideoController.Caption Not Like '%ConfigMgr%' And
  tblVideoController.Caption Not Like '%VGA%' And tblVideoController.Caption Not
  Like '%Hyper-V%' And tblNetworkAdapter.Name Not Like '%virtual%' And
  tblNetworkAdapter.Name Not Like '%VPN%' And tblFloppy.InterfaceType <> 'USB'
Order By tblAssets.AssetName

- Just a question since I am not very good at creating reports yet, is it possible to add the HDD size to this report?
Thanks by the way, this is a very helpful report.

[princesyal]

I have change accordingly but problem is still there.

[Mister_Nobody]

I think you use compact sql db instead sql express and you need convert isnull to coalesce, varchar to nvarchar. Also remove distinct in fist line.

[princesyal]

this query has error
There was an error parsing the query. [ Token line number = 1,Token line offset = 336,Token in error = Select ]
Please resolve this.

[Mister_Nobody]

Resolved Issues:
1. Support Multiple Monitors, Videocards, USB Devices, HDDs, Network Adapters attached to same asset
2. Additional info - OS Visible Memory
3. USB filter supports Russian Windows
4. Report shows only Client Windows Version(to show full report you have to remove Where tblAssets.OScode Not Like '%s')

[Mister_Nobody]

Second generation report

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblBaseBoard.Manufacturer As MBManufacturer,
  tblBaseBoard.Product,
  tblBIOS.ReleaseDate,
  tblProcessor.Name,
  tblProcessor.Caption,
  tblAssets.Memory,
  Ceiling(tblOperatingsystem.TotalVisibleMemorySize / 1024) As OSMemory,
  Stuff((Select ', ' + IsNull(it.Caption, ' ') + '/' +
    IsNull(it.VideoModeDescription, ' ') From tblVideoController As it
  Where it.Caption Not Like '%dameware%' And it.Caption Not Like '%ConfigMgr%'
    And it.Caption Not Like '%radmin%' And it.Caption Not Like '%Hyper-V%' And
    it.AssetID = tblAssets.AssetID Order By it.Caption For Xml Path('')), 1, 1,
  '') Videocards,
  Stuff((Select ', ' + IsNull(t1.MonitorManufacturer, ' ') + ' ' +
    IsNull(t1.MonitorModel, ' ') From tblMonitor t1
  Where t1.AssetID = tblAssets.AssetID For Xml Path('')), 1, 2, '') Monitors,
  Stuff((Select ', ' + IsNull(tblNetworkAdapter.Name, ' ')
  From tblNetworkAdapter
  Where tblAssets.AssetID = tblNetworkAdapter.AssetID And
    tblNetworkAdapter.Name Not Like '%virtual%' And
    tblNetworkAdapter.Name Not Like '%VPN%' For Xml Path('')), 1, 2,
  '') NetworkAdapters,
  Stuff((Select ', ' + IsNull(tblFloppy.Model, ' ') + '(' +
    IsNull(Cast(Ceiling(tblFloppy.Size / 1024 / 1024 / 1024) As varchar(30)),
    '') + 'GB)' From tblFloppy
  Where tblAssets.AssetID = tblFloppy.AssetID And tblFloppy.Size > 0
  For Xml Path('')), 1, 2, '') HDDs,
  Stuff((Select ', ' + IsNull(tblUSBDevices.Name, ' ') From tblUSBDevices
  Where tblAssets.AssetID = tblUSBDevices.AssetID And
    tblUSBDevices.Name Not Like N'%Стандарт%' And tblUSBDevices.Name Not Like
    '%HID%' And tblUSBDevices.Name Not Like '%hub%' And
    tblUSBDevices.Name Not Like N'%концентратор%' And
    tblUSBDevices.Name Not Like N'%совместим%' And
    tblUSBDevices.Name Not Like N'%устройств%' And
    tblUSBDevices.Name Not Like '%generic%' And tblUSBDevices.Name Not
    Like N'%мышь%' And tblUSBDevices.Name Not Like N'%клавиатура%' And
    tblUSBDevices.Name Not Like '%bluetooth%' And tblUSBDevices.Name Not Like
    '%headset%' And tblUSBDevices.Name Not Like '%printing support%' And
    tblUSBDevices.Name Not Like N'%поддержка%' And
    tblUSBDevices.Name Not Like '%mouse%' And tblUSBDevices.Name Not Like
    '%keyboard%' And tblUSBDevices.Name Not Like '%USB Composite Device%' And
    tblUSBDevices.Name Not Like '%USB Pointing Device%' And
    tblUSBDevices.Name Not Like '%USB Input Device%' And
    tblUSBDevices.Name Not Like '%USB Human Interface Device%' And
    tblUSBDevices.Name Not Like '%IEEE 1284.4 compatible printer%' And
    tblUSBDevices.Name Not Like '%fax%' And tblUSBDevices.Name Not Like '%EWS%'
    And tblUSBDevices.Name Not Like '%DOT4%' And tblUSBDevices.Name Not Like
    '%LEDM%' And tblUSBDevices.Name Not Like N'%фильтр%' And
    tblUSBDevices.Name Not Like '%windows%' And tblUSBDevices.Name Not
    Like N'%дисковый%' And tblUSBDevices.Name Not Like '%storage%' And
    tblUSBDevices.Name <> '' And tblUSBDevices.Name Not Like '%unknown%' And
    tblUSBDevices.Name Not Like '%HP Universal Printing%' And
    tblUSBDevices.Name Not Like '%wia driver%' For Xml Path('')), 1, 2, '') USBs
From tblAssets
  Left Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
  Left Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Where tblAssets.OScode Not Like '%s'
Order By tblAssets.AssetName