Hyper-V custom report

[Ismail]

There is an already made report showing virtual servers and their respective hosting servers but I have a few additions I would like to be inserted. Right now This is how the report is created:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName As [Hosting Server],
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblHyperVGuest.Name As [Guest Server Name],
  tblHyperVGuest.Memory,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblHyperVGuest On tblAssets.AssetID = tblHyperVGuest.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Order By [Hosting Server]

I would like the IP Address of the virtual server (Guest) and the allocated HD Space.

Your help is greatly appreciated.

[Hemoco · Accepted solution]

The query below will show the IP address of the guest and the allocated disk size.
We linked a new view of tblassets via the MAC address on TblHyperVGuestNetwork which then allows you to get information on the guest machines.
Any drives without a size allocation are filtered out.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName As [Hosting Server],
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblHyperVGuest.Name As [Guest Server Name],
  tblHyperVGuest.Memory,
  tsysOS.Image As icon,
  tblAssets1.IPAddress As [Guest IP],
  tblDiskdrives.Caption,
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
  [Size Allocated Per Drive]
From tblAssets
  Inner Join tblHyperVGuest On tblAssets.AssetID = tblHyperVGuest.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join TblHyperVGuestNetwork On tblHyperVGuest.hypervguestID =
    TblHyperVGuestNetwork.HyperVGuestID
  Left Join tblAssets tblAssets1
    On tblAssets1.Mac = TblHyperVGuestNetwork.MacAddress
  Left Join tblDiskdrives On tblAssets1.AssetID = tblDiskdrives.AssetID
Where (tblAssets1.IPAddress Is Null) Or
  (Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) Is Not
  Null)
Order By [Hosting Server]