cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
boupjof
Engaged Sweeper III
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
3 REPLIES 3
CVCO
Engaged Sweeper III
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
Lansweeper Alumni
There was a syntax error in the report (the comma before the From), which I've corrected.
CVCO
Engaged Sweeper III
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