Showing results for 
Show  only  | Search instead for 
Did you mean: 
Engaged Sweeper

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 =
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

Honored Sweeper
 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
Engaged Sweeper

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


Engaged Sweeper

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