cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Boh
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 =
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

3 REPLIES 3
Mister_Nobody
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
Boh
Engaged Sweeper

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

thanks

Boh
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