Server Report Help (ESXi host, IP Location, OS)

[RTedrow]

I'm trying to generate a report that will list all of my Windows servers with their IP Location, IP Address, Server name, and ESXi host (if virtual). I've been trying to modify a report that I found on the forums here but I'm not having much luck.

I'll paste the report below but where I'm stuck on is it is the original report had all the drives listed so I was seeing assets listed multiple times for each drive. I removed the drive space from the report but I'm still seeing assets 3-4 times. I'm also not sure how to add the ESXi host to the query.

I apologize as I'm sure this should be easy but my SQL-fu is poor.

Here is what I'm using:

Select Top 1000000 web40repIPLocationlist.IPLocation,
tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.Memory,
Case When tblAssetCustom.Model Like '%virtual%' Then 'virtual' Else 'physical'
End As VirtualCheck
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join web40repIPLocationlist On web40repIPLocationlist.AssetID =
tblAssets.AssetID
Inner Join tblDiskdrives On tblDiskdrives.AssetID = tblAssets.AssetID
Where tblAssetGroups.AssetGroup Like '%Server%'

[RTedrow · Accepted solution]

Thank you for the help! The only thing that this report does that I'd like to change is it is currently listing all of my Windows 7/Windows XP workstations. Is it a simple thing to have it only displays servers?

Either way this is amazing and I really appreciate your help on this.

EDIT: I was able to resolve this and will post the full query next week in case anyone would like to use it. I also removed the asset groups from the query as it seems we had servers in the default group as well as a custom one, so they were being listed twice.

EDIT2:
Select Top 1000000 tsysIPLocations.IPLocation As IPLocation,
tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.Memory,
Case When tblAssetCustom.Model Like '%virtual%' Then 'virtual' Else 'physical'
End As VirtualCheck,
VMWare.Hostname As [VMware Host],
HyperV.Hostname As [HyperV Host]
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Top 1000000 tblAssetMacAddress.AssetID,
tblAssets1.AssetName As Hostname
From tblAssetMacAddress
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) VMWare On VMWare.AssetID =
tblAssets.AssetID
Left Join (Select Top 1000000 tblAssetMacAddress.AssetID,
tblAssets1.AssetName As Hostname
From tblAssetMacAddress
Left Join TblHyperVGuestNetwork On TblHyperVGuestNetwork.MacAddress =
tblAssetMacAddress.Mac
Left Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
TblHyperVGuestNetwork.HyperVGuestID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblHyperVGuest.AssetID
Where tblAssets1.AssetName Is Not Null) HyperV On HyperV.AssetID =
tblAssets.AssetID
Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1

[Hemoco]

Please try this:

Select Top 1000000 tsysIPLocations.IPLocation As IPLocation,
tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Model,
tblAssets.IPAddress,
tsysOS.OSname,
tblAssets.Memory,
Case When tblAssetCustom.Model Like '%virtual%' Then 'virtual' Else 'physical'
End As VirtualCheck,
tblAssetGroups.AssetGroup,
VMWare.Hostname As [VMware Host],
HyperV.Hostname As [HyperV Host]
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Left Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select Top 1000000 tblAssetMacAddress.AssetID,
tblAssets1.AssetName As Hostname
From tblAssetMacAddress
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) VMWare On VMWare.AssetID =
tblAssets.AssetID
Left Join (Select Top 1000000 tblAssetMacAddress.AssetID,
tblAssets1.AssetName As Hostname
From tblAssetMacAddress
Left Join TblHyperVGuestNetwork On TblHyperVGuestNetwork.MacAddress =
tblAssetMacAddress.Mac
Left Join tblHyperVGuest On tblHyperVGuest.hypervguestID =
TblHyperVGuestNetwork.HyperVGuestID
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblHyperVGuest.AssetID
Where tblAssets1.AssetName Is Not Null) HyperV On HyperV.AssetID =
tblAssets.AssetID