Help - Creating a Windows Server List showing the VM they are hosted on

[gforchuk]

We currently are using an emailed report that shows us new windows server created in the last 14 days. I am looking to add to this report information regarding what VM is hosting the server but I cannot figure out the SQL to get this done. Has anyone done this already?

This is my SQL for the report we are using:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tsysOS.OSname,
tsysOS.Image As icon,
tblComputersystem.SystemType,
tblAssets.IPAddress,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Manufacturer,
tblAssets.Firstseen
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.Firstseen > GetDate() - 14 And tblAssetCustom.State = 1 And
tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
Order By tblAssets.AssetName

Any help on how to integrate the VMware information would be great.

Thank you,
Graham

[gforchuk]

Thank you very much for your help in this. That was exactly what I was looking for.

[Hemoco · Accepted solution]

Please use the following report:


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysAssetTypes.AssetTypename,
tblAssets.Domain,
tsysOS.OSname,
tsysOS.Image As icon,
tblComputersystem.SystemType,
tblAssets.IPAddress,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Manufacturer,
tblAssets.Firstseen,
tblAssets1.AssetName As vmwarehost
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblVmwareGuestNetwork
On tblAssets.Mac = tblVmwareGuestNetwork.MacAddress
Inner Join tblVmwareGuest On tblVmwareGuest.GuestID =
tblVmwareGuestNetwork.GuestID
Inner Join tblAssets tblAssets1 On tblAssets1.AssetID = tblVmwareGuest.AssetID
Where tblAssets.Firstseen > GetDate() - 14 And tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1