Report for All Server Assets

[RandomITDude232]

Good afternoon I am trying to create a report that will get a list of all servers that we have within our datacenter this report should also include the make, model, serial number,  type and number of processors, amount of ram, and other custom fields.

I created one already that does almost 90% of the work I'm looking for, but I'm running into an issue where it is not finding the VMware ESXi servers so it can't find our vSAN nodes If someone could take a look at the following code that I put down and let me know what I'm doing wrong as to why I'm not seeing the vSAN nodes I'd be grateful

Select Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.SerialNumberScanned As [Serial Number],
tblAssets.Description,
tblAssetCustom.Comments,
tblAssets.Processor As [Processor Type],
tblAssets.NrProcessors As [# of Processors],
Cast(Round(CorrectMemory.Memory / 1024, 2, 1) As decimal(10,1)) As
[Memory (GB)],
Cast(Round(tblPhysicalMemoryArray.MaxCapacity / 1024 / 1024, 2,
1) As decimal(10,1)) As [Max Memory Capacity],
Cast(CorrectMemory.Used As numeric) As [Slots used],
tblPhysicalMemoryArray.MemoryDevices As [Slots available],
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.Custom1 As Row#,
tblAssetCustom.Custom2 As Rack#,
tblAssetCustom.Custom3 As [Rack Position],
tblAssetCustom.Custom4 As [Server Size],
tblAssetCustom.Custom5 As [PP Warranty Start],
tblAssetCustom.Custom6 As [PP Warranty End],
tblAssetCustom.Custom7 As [PP Warranty #],
tblAssetCustom.Custom8 As [Verified in Zabbix],
tblAssetCustom.Custom9 As [Rubrik Backup Verified],
tblAssetCustom.Custom10 As PO#,
tblAssetCustom.Custom11 As FOP#,
tblAssetCustom.Custom12 As [FOP Approver]
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblPhysicalMemoryArray On
tblAssets.AssetID = tblPhysicalMemoryArray.AssetID
Inner Join (Select tblAssets.AssetID,
Sum(Ceiling(tblPhysicalMemory.Capacity / 1024 / 1024)) As Memory,
Count(tblPhysicalMemory.Win32_PhysicalMemoryid) As Used
From tblAssets
Left Outer Join (TsysMemorytypes
Right Outer Join tblPhysicalMemory On TsysMemorytypes.Memorytype =
tblPhysicalMemory.MemoryType) On tblAssets.AssetID =
tblPhysicalMemory.AssetID
Group By tblAssets.AssetID,
tblPhysicalMemory.MemoryType
Having tblPhysicalMemory.MemoryType <> 11) CorrectMemory On
CorrectMemory.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName,
[Serial Number]

[Mister_Nobody]

You have to modify last "where" to remove filter for vmware and Microsoft "hardware" 

[Mister_Nobody]

I have created such report for hardware server inventory.
It supports Windows, Linux and ESXi: 

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.osrelease,
  tblVmwareInfo.Version) As OS,
  tblAssets.IPAddress,
  tblAssets.Mac,
  tblAssets.Processor As CPUModel,
  Coalesce(tblVmwareInfo.CpuMhz, tblProcessor.maxclockspeed) As CPUSpeed,
  tblAssets.NrProcessors As CPU,
  Cast(tblAssets.Memory As BigInt) As RAM,
  Coalesce((Select Sum(Cast(Cast(tblLinuxHardDisks.Size As BigInt) /
      1024 As Numeric)) From tblLinuxHardDisks
    Where tblLinuxHardDisks.AssetID = tblAssets.AssetID And
      tblLinuxHardDisks.filesystem Like '/dev/%'), '0') + Coalesce((Select
      Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 As Numeric))
    From tblDiskdrives
    Where tblDiskdrives.AssetID = tblAssets.AssetID), '0') As HDDSize,
  tblAssetCustom.Manufacturer As Manufacturer,
  tblAssetCustom.Model As Model,
  Coalesce(tblBaseBoard.product, tblLinuxBaseBoard.productname) As baseboard,
  Coalesce(tblBaseBoard.Serialnumber, tblLinuxBaseBoard.Serialnumber) As
  baseboard_SN,
  tblAssetCustom.Serialnumber,
  Coalesce(tblVmwareInfo.Biosdate, tblBIOS.releasedate,
  tblLinuxBios.releasedate) As BiosDate
From tblAssets
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
  Left Join tblBaseBoard On tblAssets.AssetID = tblBaseBoard.AssetID
  Left Join tblLinuxBaseBoard On tblAssets.AssetID = tblLinuxBaseBoard.AssetID
  Left Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Left Join tblLinuxProcessors On tblAssets.AssetID = tblLinuxProcessors.AssetID
  Left Join tblLinuxBios On tblAssets.AssetID = tblLinuxBios.AssetID
  Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Where (tblAssets.Processor Like '%xeon%' Or tblAssets.Processor Like '%epyc%')
  And tblAssetCustom.Manufacturer Not Like '%vmware%' And
  tblAssetCustom.Manufacturer Not Like '%microsoft%' And
  IsNull(tblAssetCustom.Manufacturer, '') <> ''
Order By tblAssets.assetName

[RandomITDude232]

This is an amazing code i thank you so much KUDOS. One thing i noticed is that it is not grabbing the virutal machines from VMWare it is grabbing the ESXi Servers but not the VM's. Do you know or have one that does all of the above but includes those VM's?

Thank you so much for your reply