cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Mister_Nobody
Honored Sweeper
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
Thank you Brandon this is perfect!
cori406
Engaged Sweeper II
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
Champion Sweeper III
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
I have change accordingly but problem is still there.
Mister_Nobody
Honored Sweeper
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
Engaged Sweeper
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
Honored Sweeper
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
Honored Sweeper
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