Vmware ESXi inventory

[boupjof]

I made this report for see witch ESXi server is up to date and witch on is not.
If it can help someone.

Change the version number in the like check for match your infra

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Case
    When tblVmwareInfo.Version Like '%VMware ESXi 6.5.0 build-5310538%' Then
    '#00ff15' Else '#f77171' End As backgroundcolor,
  tblVmwareInfo.Version,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tsysAssetTypes.AssetTypename As Type,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
  Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Order By tblAssets.AssetName

[CVCO]

Built this report with some feedback from Lansweeper support if it's helpful to anyone else:

Select Top 1000000 tblAssets.AssetID,
  Case 
	When tblVmwareInfo.Version Like '%5.5%7504623%' Then '#00FF00'
	When tblVmwareInfo.Version Like '%6.0%7504637%' Then '#00FF00'
	When tblVmwareInfo.Version Like '%6.5%7526125%' Then '#00FF00'	
    Else '#F0E68C' End As backgroundcolor,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetName,
  tblVmwareInfo.Version As ESXiVersion,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblVmwareInfo.BiosVersion,
  tblVmwareInfo.BiosDate,
  tblAssets.Processor,
  tblAssetCustom.PurchaseDate,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblstate.Statename As State
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
  Inner Join tblstate On tblstate.State = tblAssetCustom.State
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Order By tblAssets.AssetName

[Susan_A]

There was a syntax error in the report (the comma before the From), which I've corrected.

[CVCO]

Thanks this looks like a useful report to build on for ESXi hosts. Is there any recommendation for syntax changes in the query on Lansweeper v6.0.100.98? This is the result:

Invalid SELECT statement. Unexpected token "From" at line 15, pos 1.: Unexpected token "From" at line 15, column 1