This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Hardware Inventory

Mister_Nobody
Honored Sweeper II

‎10-05-2016 06:03 AM

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
8 REPLIES 8
cori406
Engaged Sweeper II

‎04-16-2021 05:05 PM

Thank you Brandon this is perfect!
0 Kudos
cori406
Engaged Sweeper II

‎04-13-2021 05:56 PM

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.
0 Kudos
brandon_jones
Champion Sweeper III
In response to cori406

‎04-13-2021 08:07 PM

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
Engaged Sweeper

‎05-05-2017 06:47 AM

I have change accordingly but problem is still there.
0 Kudos
Mister_Nobody
Honored Sweeper II

‎03-22-2017 02:54 PM

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.
0 Kudos
princesyal
Engaged Sweeper

‎03-22-2017 09:51 AM

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.
0 Kudos
Mister_Nobody
Honored Sweeper II

‎03-03-2017 04:37 PM

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')
0 Kudos
Mister_Nobody
Honored Sweeper II

‎03-03-2017 04:30 PM

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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now