Linux and Windows Hardware Inventory Report

[Mister_Nobody]

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.

[Mister_Nobody · Accepted solution]

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, '') <> ''

[Obi_1_Cinobi]

@Mister_Nobody, awesome job again!