Notification

Icon
Error

Hardware Inventory

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

Mister Nobody

Member Original PosterPosts: 50
3
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: 139  
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 Using tblO365User report for devices Out of warranty
by  QueryLSTech   Go to last post Go to first unread
Last post: Yesterday at 5:15:37 PM(UTC)
Lansweeper Duplicate assets, random monitor unique keys
by  kloosterd  
Go to last post Go to first unread
Last post: Yesterday at 1:00:34 PM(UTC)
Lansweeper Scanning - nothing appears in the queue
by  LS IT Admins   Go to last post Go to first unread
Last post: Yesterday at 11:08:22 AM(UTC)
Lansweeper Broken scanning of AD
by  LS IT Admins  
Go to last post Go to first unread
Last post: Yesterday at 10:59:35 AM(UTC)
Lansweeper New ticket auto-assignment & default state
by  Brandon   Go to last post Go to first unread
Last post: 5/13/2021 5:21:31 PM(UTC)
Lansweeper Automatic Follow-Up for Tickets
by  Francis Lee Mondia - Endace  
Go to last post Go to first unread
Last post: 5/12/2021 11:06:51 PM(UTC)
Lansweeper Can't see devices on Lansweeper
by  vqT4cDoP9iXyMZwoDUWU   Go to last post Go to first unread
Last post: 5/12/2021 8:33:21 PM(UTC)
Lansweeper LAPS managed password
by  SystemAdmin  
Go to last post Go to first unread
Last post: 5/12/2021 6:08:42 PM(UTC)