Adding a Column for server report - ESXI server that the server is tied to

[cpletcher]

I have made numerous reports in the past, added and removed columns for all sorts of different instances, but this one has me stumped. The current report I am trying to add a column on that will show what ESXI server that the current server is tied to, but every attempt I have tried, it just breaks the report. (It either will not show what ESXI/VM that the server is hosted on, or it completely removes all servers in the report.) I am trying to remove the "Manufacture" column, and change it to "Vmware Host" and then have it show what VM server it is on. I currently have 2 reports to show all Servers with certain columns for reporting purposes, and then I have another report that shows all of our ESXI servers with additional columns, but would like to be able to see what ESXI server they are hosted on. Below is the report I am working with, and after many failed attempts I am at a loss. I figured it would be something simple to change/add but have spent way too much time trying to figure it out. Any tips, or advice would be appreciated. Below is the report I am trying to add this column to.

Thank you!

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.IPAddress,
tsysOS.OSname As OS,
tblAssetCustom.Custom2 As Backups,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.Domain,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
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 Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblComputersystem.AssetID And tblAssets1.AssetID =
tblAssetCustom.AssetID And tsysIPLocations.LocationID =
tblAssets1.LocationID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Mister_Nobody]

Try this:

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.Domain,
  tblAssets.AssetName,
  tblAssets2.AssetName As Host,
  tblAssets.IPAddress,
  tsysOS.OSname As OS,
  tblAssetCustom.Custom2 As Backups,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblAssets.Userdomain,
  tblAssets.Username,
    tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
   tblAssets.Firstseen,
  tblAssets.Lastseen
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 Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
      And tsysIPLocations.EndIP >= tblAssets.IPNumeric
  Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
  Inner Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
  Inner Join tblAssets As tblAssets2 On tblAssets2.AssetID =
      tblVmwareInfo.AssetID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[cpletcher]

So close! This added the Column Host next to AssetName, which is exactly what I was looking for! The only issue though is that it is only showing VM Servers, and removed the Physical servers. I did remove the "tblAssets.Domain," since it popped up first in the list. I compared your report next to mine to try and figure out why it removed my physical servers, but I can't figure out why it would have removed a physical server on the report. I even tried to add in 

Inner Join tblAssets tblAssets1 On
tblAssets1.AssetID = tblComputersystem.AssetID And tblAssets1.AssetID =
tblAssetCustom.AssetID And tsysIPLocations.LocationID =
tblAssets1.LocationID

but it still didn't include any physical servers. This is progress though! Further than I got before. I will past what I have on the report below. (It's just the minor change of removing the Domain column.) Is there something missing that would remove a physical server, or will this report only show VM servers?

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets2.AssetName As Host,
tblAssets.IPAddress,
tsysOS.OSname As OS,
tblAssetCustom.Custom2 As Backups,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Userdomain,
tblAssets.Username,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
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 Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Inner Join tblVmwareInfo On tblVmwareGuest.HostID = tblVmwareInfo.VmwareID
Inner Join tblAssets As tblAssets2 On tblAssets2.AssetID =
tblVmwareInfo.AssetID
Where tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Mister_Nobody]

You can change inner to left in the vmware section 

[cpletcher]

Tested, and no go. Tried changing all 3 to Left, and it listed every PC in our system. I tried Left on the first, Inner on the 2nd two, and it only showed VM. Tried Left Inner Left, and all other combinations and still only showed VM's. I need to take a refresher course for SQL lol. I have always hated it, and still do. 

[Mister_Nobody]

Try my report 

https://community.lansweeper.com/t5/general-discussions/vmware-guest-inventory/td-p/67060