report to show asset name, #CPU, memory in GB, power status and total disk allocated (ALL DRIVES)

[Boh]

trying to see if anyone can help me on this custom report im trying to create.

requirements are

• asset name
• number of cpu
• memory in GB
• power status
• total disk allocated (ALL DRIVES)

so far i am able to generate name, # of cpu, memory (but not in GB) and hard disk space but it is shwoing as individual drive. wondering if anyone can help

Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName As Name,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.NrProcessors As NumCpu,
tblAssets.Memory As MemoryGB,
Cast(tblVolume.Capacity / 1024 / 1024 / 1024 As numeric) As HardDiskSizeGB
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblVolume On tblAssets.AssetID = tblVolume.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Where (tblAssets.IPAddress Like '10.51.%' Or tblAssets.IPAddress Like '10.31.%')
And tblVolume.DriveLetter Is Not Null
Order By Name

[Mister_Nobody]

 Coalesce((Select Sum(Cast(Cast(tblLinuxHardDisks.Size As BigInt) /
      1024 As bigint)) From tblLinuxHardDisks
    Where tblLinuxHardDisks.AssetID = tblAssets.AssetID And
      (tblLinuxHardDisks.filesystem Like '/dev/s%' Or
        tblLinuxHardDisks.filesystem Like '/dev/mapper/%')), '0') +
  Coalesce((Select Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 /
      1024 As bigint)) From tblDiskdrives
    Where tblDiskdrives.AssetID = tblAssets.AssetID), '0') As HDDSize

[Boh]

reason for this report is for internal charges/cost allocation.

thanks

[Boh]

i can use  Cast(tblAssets.Memory / 1024 As numeric) As MemoryGB to convert the memory but it would not let me to do the following cast for disk space

Cast(tblVolume.Capacity / 1024 / 1024 / 1024 As numeric) As HardDiskSizeGB