Notification

Icon
Error

Hardware Inventory

Posted: Wednesday, October 5, 2016 5:03:01 AM(UTC)
Mister Nobody

Mister Nobody

Member Original PosterPosts: 50
4
Like
I decided to publish some of my reports.

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

Code:
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
Mister Nobody
#1Mister Nobody Member Original PosterPosts: 50  
posted: 3/3/2017 4:30:59 PM(UTC)
Second generation report
Code:
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
Mister Nobody
#2Mister Nobody Member Original PosterPosts: 50  
posted: 3/3/2017 4:37:29 PM(UTC)
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')
princesyal
#3princesyal Member Posts: 2  
posted: 3/22/2017 9:51:00 AM(UTC)
Shame on you 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
#4Mister Nobody Member Original PosterPosts: 50  
posted: 3/22/2017 2:54:19 PM(UTC)
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
#5princesyal Member Posts: 2  
posted: 5/5/2017 5:47:03 AM(UTC)
I have change accordingly but problem is still there.
Cori
#6Cori Member Posts: 4  
posted: 4/13/2021 4:56:04 PM(UTC)
Originally Posted by: Mister Nobody Go to Quoted Post
I decided to publish some of my reports.

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

Code:
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. Angel
Brandon
#7Brandon Member Posts: 169  
posted: 4/13/2021 7:07:50 PM(UTC)
Try this:
Quote:
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


Originally Posted by: Cori Go to Quoted Post
Originally Posted by: Mister Nobody Go to Quoted Post
I decided to publish some of my reports.

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

Code:
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. Angel


Cori
#8Cori Member Posts: 4  
posted: 4/16/2021 4:05:02 PM(UTC)
Thank you Brandon this is perfect! Angel

Active Discussions

Lansweeper Monitor history showing only last monitors
by  cross_eur   Go to last post Go to first unread
Last post: 7/23/2021 6:06:51 PM(UTC)
Lansweeper Report Login time reduces number of computers by 300
by  cross_eur  
Go to last post Go to first unread
Last post: 7/23/2021 6:05:42 PM(UTC)
Lansweeper Merging 2 reports
by  Apaulcolypse   Go to last post Go to first unread
Last post: 7/22/2021 10:02:59 PM(UTC)
Lansweeper HELP - Add Registry Key Values to Asset Report
by  Apaulcolypse  
Go to last post Go to first unread
Last post: 7/22/2021 9:26:43 PM(UTC)
Lansweeper Can I request a custom report here?
by  Brian G   Go to last post Go to first unread
Last post: 7/22/2021 7:20:56 PM(UTC)
Lansweeper List all users with E-mail address
by  Brandon  
Go to last post Go to first unread
Last post: 7/21/2021 7:06:36 PM(UTC)
Lansweeper Identifying users of Windows legacy authentication
by  Baronet   Go to last post Go to first unread
Last post: 7/21/2021 5:26:38 PM(UTC)
Lansweeper Windows Version different between reports
by  RC62N  
Go to last post Go to first unread
Last post: 7/21/2021 3:27:04 PM(UTC)