- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-02-2025
10:16 AM
- last edited
a month ago
by
Obi_1_Cinobi
I am developing new generation of My Hardware Report.
Previous version https://community.lansweeper.com/t5/reports-analytics/hardware-inventory/m-p/40358
New version supports Linux and Windows assets in the same report.
Report shows processor, memory size, NIC, disks, videocards and monitors for Windows.
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease, '') As OS,
tblassets.Processor As CPU,
Coalesce(Stuff((Select ', ' + IsNull(it.Caption, ' ')
From tblVideoController As it
Where it.Caption Not Like '%indirect%' And it.Caption Not Like '%dameware%'
And it.Caption Not Like '%ConfigMgr%' And it.Caption Not Like '%remote%'
And it.Caption Not Like '%radmin%' And it.AssetID = tblassets.AssetID
Order By it.Caption For Xml Path('')), 1, 1, ''), Stuff((Select ', ' +
+IsNull(it.Manufacturer, ' ') + ' ' + IsNull(it.name, ' ')
From tblLinuxGraphicsCards As it
Where it.AssetID = tblassets.AssetID Order By it.manufacturer
For Xml Path('')), 1, 1, '')) Videocards,
tblassets.Memory As Memory,
Coalesce(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
And tblFloppy.interfacetype <> 'USB' For Xml Path('')), 1, 2, ''),
Stuff((Select ', ' + IsNull(tblLinuxPhysicalHardDisk.Model, ' ') + '(' +
IsNull(Cast(Ceiling(tblLinuxPhysicalHardDisk.Size / 1024 / 1024 /
1024) As varchar(30)), '') + 'GB)' From tblLinuxPhysicalHardDisk
Where tblassets.AssetID = tblLinuxPhysicalHardDisk.AssetID And
tblLinuxPhysicalHardDisk.Size > 0 And tblLinuxPhysicalHardDisk.Name Not
Like '/dev/zram%' And tblLinuxPhysicalHardDisk.Name Not Like '/dev/fd%'
For Xml Path('')), 1, 2, '')) Disks,
Coalesce(Stuff((Select ', ' + IsNull(it.name, ' ')
From tblNetworkAdapter As it
Where it.name Not Like '%tap%' And it.name Not Like '%vpn%' And
it.name Not Like '%virtualbox host-only%' And
it.name Not Like 'svn adapter%' And it.name Not Like '%for vmnet%' And
it.name Not Like '%miniport%' And it.name Not Like '%ppoe%' And
it.name Not Like '%ppp%' And it.AssetID = tblassets.AssetID Order By
it.name For Xml Path('')), 1, 1, ''), Stuff((Select ', ' +
IsNull(itl.name, ' ') From tblLinuxPciCards As itl
Where itl.AssetID = tblassets.AssetID And itl.Type = 'Ethernet controller'
Order By itl.name For Xml Path('')), 1, 1, '')) NICs,
Stuff((Select ', ' + IsNull(t1.MonitorManufacturer, ' ') + ' ' +
IsNull(t1.MonitorModel, ' ') From tblMonitor t1
Where t1.AssetID = tblassets.AssetID For Xml Path('')), 1, 2, '') Monitors
From tblassets
Left Join tblLinuxSystem On tblassets.AssetID = tblLinuxSystem.AssetID
Left Join tblOperatingsystem On tblassets.AssetID = tblOperatingsystem.AssetID
Where Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease, '') <> ''
If you have wishes to improve report then post your suggestions with LS data.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-07-2025 10:27 AM
Small stuff fix, add motherboard info:
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease, '') As OS,
Coalesce(tblBaseBoard.Manufacturer + ' ' + tblBaseBoard.Product,
tblLinuxBaseBoard.Manufacturer + ' ' + tblLinuxBaseBoard.ProductName,
'NA') As MB,
tblassets.Processor As CPU,
Coalesce(Stuff((Select ', ' + IsNull(it.Caption, ' ')
From tblVideoController As it
Where it.Caption Not Like '%indirect%' And it.Caption Not Like '%dameware%'
And it.Caption Not Like '%ConfigMgr%' And it.Caption Not Like '%remote%'
And it.Caption Not Like '%radmin%' And it.AssetID = tblassets.AssetID
Order By it.Caption For Xml Path('')), 1, 2, ''), Stuff((Select ', ' +
+IsNull(it.Manufacturer, ' ') + ' ' + IsNull(it.name, ' ')
From tblLinuxGraphicsCards As it
Where it.AssetID = tblassets.AssetID Order By it.manufacturer
For Xml Path('')), 1, 2, '')) Videocards,
tblassets.Memory As Memory,
Coalesce(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
And tblFloppy.interfacetype <> 'USB' For Xml Path('')), 1, 2, ''),
Stuff((Select ', ' + IsNull(tblLinuxPhysicalHardDisk.Model, ' ') + '(' +
IsNull(Cast(Ceiling(tblLinuxPhysicalHardDisk.Size / 1024 / 1024 /
1024) As varchar(30)), '') + 'GB)' From tblLinuxPhysicalHardDisk
Where tblassets.AssetID = tblLinuxPhysicalHardDisk.AssetID And
tblLinuxPhysicalHardDisk.Size > 0 And tblLinuxPhysicalHardDisk.Name Not
Like '/dev/zram%' And tblLinuxPhysicalHardDisk.Name Not Like '/dev/fd%'
For Xml Path('')), 1, 2, '')) Disks,
Coalesce(Stuff((Select ', ' + IsNull(it.name, ' ')
From tblNetworkAdapter As it
Where it.name Not Like '%tap%' And it.name Not Like '%vpn%' And
it.name Not Like '%virtualbox host-only%' And
it.name Not Like 'svn adapter%' And it.name Not Like '%for vmnet%' And
it.name Not Like '%miniport%' And it.name Not Like '%ppoe%' And
it.name Not Like '%ppp%' And it.AssetID = tblassets.AssetID Order By
it.name For Xml Path('')), 1, 2, ''), Stuff((Select ', ' +
IsNull(itl.name, ' ') From tblLinuxPciCards As itl
Where itl.AssetID = tblassets.AssetID And itl.Type = 'Ethernet controller'
Order By itl.name For Xml Path('')), 1, 2, '')) NICs,
Stuff((Select ', ' + IsNull(t1.MonitorManufacturer, ' ') + ' ' +
IsNull(t1.MonitorModel, ' ') From tblMonitor t1
Where t1.AssetID = tblassets.AssetID For Xml Path('')), 1, 2, '') Monitors
From tblassets
Left Join tblLinuxSystem On tblassets.AssetID = tblLinuxSystem.AssetID
Left Join tblOperatingsystem On tblassets.AssetID = tblOperatingsystem.AssetID
Left Join tblBaseBoard On tblassets.AssetID = tblBaseBoard.AssetID
Left Join tblLinuxBaseBoard On tblassets.AssetID = tblLinuxBaseBoard.AssetID
Where Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease, '') <> ''
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-07-2025 10:27 AM
Small stuff fix, add motherboard info:
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease, '') As OS,
Coalesce(tblBaseBoard.Manufacturer + ' ' + tblBaseBoard.Product,
tblLinuxBaseBoard.Manufacturer + ' ' + tblLinuxBaseBoard.ProductName,
'NA') As MB,
tblassets.Processor As CPU,
Coalesce(Stuff((Select ', ' + IsNull(it.Caption, ' ')
From tblVideoController As it
Where it.Caption Not Like '%indirect%' And it.Caption Not Like '%dameware%'
And it.Caption Not Like '%ConfigMgr%' And it.Caption Not Like '%remote%'
And it.Caption Not Like '%radmin%' And it.AssetID = tblassets.AssetID
Order By it.Caption For Xml Path('')), 1, 2, ''), Stuff((Select ', ' +
+IsNull(it.Manufacturer, ' ') + ' ' + IsNull(it.name, ' ')
From tblLinuxGraphicsCards As it
Where it.AssetID = tblassets.AssetID Order By it.manufacturer
For Xml Path('')), 1, 2, '')) Videocards,
tblassets.Memory As Memory,
Coalesce(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
And tblFloppy.interfacetype <> 'USB' For Xml Path('')), 1, 2, ''),
Stuff((Select ', ' + IsNull(tblLinuxPhysicalHardDisk.Model, ' ') + '(' +
IsNull(Cast(Ceiling(tblLinuxPhysicalHardDisk.Size / 1024 / 1024 /
1024) As varchar(30)), '') + 'GB)' From tblLinuxPhysicalHardDisk
Where tblassets.AssetID = tblLinuxPhysicalHardDisk.AssetID And
tblLinuxPhysicalHardDisk.Size > 0 And tblLinuxPhysicalHardDisk.Name Not
Like '/dev/zram%' And tblLinuxPhysicalHardDisk.Name Not Like '/dev/fd%'
For Xml Path('')), 1, 2, '')) Disks,
Coalesce(Stuff((Select ', ' + IsNull(it.name, ' ')
From tblNetworkAdapter As it
Where it.name Not Like '%tap%' And it.name Not Like '%vpn%' And
it.name Not Like '%virtualbox host-only%' And
it.name Not Like 'svn adapter%' And it.name Not Like '%for vmnet%' And
it.name Not Like '%miniport%' And it.name Not Like '%ppoe%' And
it.name Not Like '%ppp%' And it.AssetID = tblassets.AssetID Order By
it.name For Xml Path('')), 1, 2, ''), Stuff((Select ', ' +
IsNull(itl.name, ' ') From tblLinuxPciCards As itl
Where itl.AssetID = tblassets.AssetID And itl.Type = 'Ethernet controller'
Order By itl.name For Xml Path('')), 1, 2, '')) NICs,
Stuff((Select ', ' + IsNull(t1.MonitorManufacturer, ' ') + ' ' +
IsNull(t1.MonitorModel, ' ') From tblMonitor t1
Where t1.AssetID = tblassets.AssetID For Xml Path('')), 1, 2, '') Monitors
From tblassets
Left Join tblLinuxSystem On tblassets.AssetID = tblLinuxSystem.AssetID
Left Join tblOperatingsystem On tblassets.AssetID = tblOperatingsystem.AssetID
Left Join tblBaseBoard On tblassets.AssetID = tblBaseBoard.AssetID
Left Join tblLinuxBaseBoard On tblassets.AssetID = tblLinuxBaseBoard.AssetID
Where Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease, '') <> ''
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-04-2025 11:18 AM
@Mister_Nobody, awesome job again!
