Report on Windows/Linux Servers including ESX server hostname

[cbtg2006]

So we're scanning Windows and VMWare in our environment. I'd like to be able to modify a report on all Windows servers to include the last scanned 'home' ESX server host name.

This will help me ensure compliance with a few static/ring-fenced machines that sit on specifically licensed clusters.

Example query we're using below (for SQL servers) if you can advise on what needs to be modified, looks like I need to loo,kup information from both tblVMwareInfo and tblVMwareGuest?

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Domain,
  VirtualMachine = Case tblAssetCustom.Model
    When 'VMware Virtual Platform' Then 'YES' When 'Virtual Machine' Then 'YES'
    Else 'NO' End,
  tsysOS.OSname As 'Operating System',
  tblADComputers.Description As 'AD Description',
  tsysOS.Image As icon,
  tblAssets.NrProcessors As [# Processors],
  tblPROCESSOR.NumberOfCores * tblAssets.NrProcessors As [# Total Cores],
  tblSqlServers.displayVersion As [SQL Version],
  tblSqlServers.skuName As [SQL Edition]
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 tblAssets.IPNumeric >=
    tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Inner Join tblPROCESSOR On tblAssets.AssetID = tblPROCESSOR.AssetID
  Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID

 Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique

Thanks,

Chris

[Hemoco · Accepted solution]

Please use the following report:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Domain,
VirtualMachine = Case tblAssetCustom.Model
When 'VMware Virtual Platform' Then 'YES' When 'Virtual Machine' Then 'YES'
Else 'NO' End,
tsysOS.OSname As 'Operating System',
tblADComputers.Description As 'AD Description',
tsysOS.Image As icon,
tblAssets.NrProcessors As [# Processors],
tblProcessor.NumberOfCores * tblAssets.NrProcessors As [# Total Cores],
tblSqlServers.displayVersion As [SQL Version],
tblSqlServers.skuName As [SQL Edition],
SubQuery1.Hostname
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 tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Inner Join tblSqlServers On tblAssets.AssetID = tblSqlServers.AssetID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join (Select tblAssets.AssetID,
tblAssets1.AssetName As Hostname
From tblAssets
Inner Join tblAssetMacAddress On tblAssetMacAddress.AssetID =
tblAssets.AssetID
Left Join tblVmwareGuestNetwork On tblVmwareGuestNetwork.MacAddress =
tblAssetMacAddress.Mac
Left Join tblVmwareGuest On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblVmwareGuest.AssetID
Where tblAssets1.AssetName Is Not Null) SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique