Adding custom sizes to HDD size and Memory

[RMH]

I have most of what I need for a comprehensive hardware audit on my current report, although I am having trouble adding in the HDD size and total free space. Also I would like the Memory size and HDD to be displayed in GB instead of bytes. I have tried other fixes in this forum and in the Report Center, but they did not work in the intended way for my purposes.
Thank you for any help.

This is what I have so far:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Case When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
Else 'Laptop' End As Type,
tblOperatingsystem.Caption As [Operating system],
tblOperatingsystem.ServicePackMajorVersion As [Service Pack],
tblOperatingsystem.WindowsDirectory As [Windows folder],
tblOperatingsystem.RegisteredUser As [Registered user],
tblOperatingsystem.Organization,
tblAssets.Domain,
tblComputersystem.SystemType,
tblOperatingsystem.SerialNumber As [OS serial No],
tblOperatingsystem.InstallDate As [OS Install date],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Service tag],
TsysChassisTypes.ChassisName As Chassis,
tblProcessor.Caption As Processor,
tblProcessor.NumberOfCores,
tblProcessor.NumberOfLogicalProcessors,
tblProcessor.Manufacturer As Manufacturer1,
tblProcessor.DataWidth,
tblProcessor.L2CacheSize,
tblProcessor.MaxClockSpeed,
tblVideoController.Caption As [Video card],
tblBIOS.Caption As BIOS,
tblBIOS.Manufacturer As [BIOS manufacturer],
tblBIOS.Version As [BIOS version],
tblBIOS.SerialNumber As [BIOS Serial No],
CorrectMemory.Memory,
Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) As [Max Memory Capacity],
Cast(CorrectMemory.Used As numeric) As [Slots used],
tblPhysicalMemoryArray.MemoryDevices As [Slots available],
tblDiskdrives.Caption,
tblDiskdrives.Description,
tblDiskdrives.DriveType,
tblDiskdrives.FileSystem,
tblDiskdrives.Freespace
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
Left Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Left Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
Left Join tblVideoController On tblAssets.AssetID = tblVideoController.AssetID
Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Left Join tblPhysicalMemoryArray On tblAssets.AssetID =
tblPhysicalMemoryArray.AssetID
Inner Join (Select tblAssets.AssetID,
Sum(Ceiling(tblPhysicalMemory.Capacity / 1024 / 1024)) As Memory,
Count(tblPhysicalMemory.Win32_PhysicalMemoryid) As Used
From tblAssets
Left Outer Join (TsysMemorytypes
Right Outer Join tblPhysicalMemory On TsysMemorytypes.Memorytype =
tblPhysicalMemory.MemoryType) On tblAssets.AssetID =
tblPhysicalMemory.AssetID
Group By tblAssets.AssetID,
tblPhysicalMemory.MemoryType
Having tblPhysicalMemory.MemoryType <> 11) CorrectMemory
On CorrectMemory.AssetID = tblAssets.AssetID
Left Join tblPortableBattery On tblPortableBattery.AssetID = tblAssets.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2
Order By tblAssets.AssetName

[Daniel_B · Accepted solution]

The following report uses a ROUND function for the RAM and displays only HDDs and no floppy's etc.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Case When Coalesce(tblPortableBattery.AssetID, 0) = 0 Then 'Desktop'
    Else 'Laptop' End As Type,
  tblOperatingsystem.Caption As [Operating system],
  tblOperatingsystem.ServicePackMajorVersion As [Service Pack],
  tblOperatingsystem.WindowsDirectory As [Windows folder],
  tblOperatingsystem.RegisteredUser As [Registered user],
  tblOperatingsystem.Organization,
  tblAssets.Domain,
  tblComputersystem.SystemType,
  tblOperatingsystem.SerialNumber As [OS serial No],
  tblOperatingsystem.InstallDate As [OS Install date],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber As [Service tag],
  TsysChassisTypes.ChassisName As Chassis,
  tblProcessor.Caption As Processor,
  tblProcessor.NumberOfCores,
  tblProcessor.NumberOfLogicalProcessors,
  tblProcessor.Manufacturer As Manufacturer1,
  tblProcessor.DataWidth,
  tblProcessor.L2CacheSize,
  tblProcessor.MaxClockSpeed,
  tblVideoController.Caption As [Video card],
  tblBIOS.Caption As BIOS,
  tblBIOS.Manufacturer As [BIOS manufacturer],
  tblBIOS.Version As [BIOS version],
  tblBIOS.SerialNumber As [BIOS Serial No],
  Cast(Round(CorrectMemory.Memory / 1024, 2, 1) As decimal(10,1)) As
  [Memory (GB)],
  Cast(Round(tblPhysicalMemoryArray.MaxCapacity / 1024 / 1024, 2, 1) 
  As decimal(10,1)) As [Max Memory Capacity],
  Cast(CorrectMemory.Used As numeric) As [Slots used],
  tblPhysicalMemoryArray.MemoryDevices As [Slots available],
  tDiskdrives.Caption as [HD Caption],
  tDiskdrives.Description,
  tDiskdrives.FileSystem,
  tDiskdrives.Size as [HD size (GB)],
  tDiskdrives.freespace As [free space (GB)]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Left Join TsysChassisTypes On TsysChassisTypes.Chassistype =
    tblSystemEnclosure.ChassisTypes
  Left Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID
  Left Join tblVideoController On tblAssets.AssetID = tblVideoController.AssetID
  Left Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Left Join tblPhysicalMemoryArray On tblAssets.AssetID =
    tblPhysicalMemoryArray.AssetID
  Inner Join (Select tblAssets.AssetID,
    Sum(Ceiling(tblPhysicalMemory.Capacity / 1024 / 1024)) As Memory,
    Count(tblPhysicalMemory.Win32_PhysicalMemoryid) As Used
  From tblAssets
    Left Outer Join (TsysMemorytypes
    Right Outer Join tblPhysicalMemory On TsysMemorytypes.Memorytype =
      tblPhysicalMemory.MemoryType) On tblAssets.AssetID =
      tblPhysicalMemory.AssetID
  Group By tblAssets.AssetID,
    tblPhysicalMemory.MemoryType
  Having tblPhysicalMemory.MemoryType <> 11) CorrectMemory
    On CorrectMemory.AssetID = tblAssets.AssetID
  Left Join tblPortableBattery On tblPortableBattery.AssetID = tblAssets.AssetID
  Inner Join (Select tblDiskdrives.AssetID,
    tblDiskdrives.Caption,
    tblDiskdrives.Description,
    tblDiskdrives.FileSystem,
    Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 /
    1024 As Numeric) As freespace,
    Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 /
    1024 As Numeric) As Size
  From tblDiskdrives
  Where tblDiskdrives.DriveType = 3) tDiskdrives On tblAssets.AssetID =
    tDiskdrives.AssetID
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole < 2
Order By tblAssets.AssetName,
[HD Caption]

[RMH]

RC62N,

Thank you so much for your response. I have tried to insert the code you use into the report, but it keeps giving me Errors that "(" is unexpected and the "tblDiskdrives" is unexpected. Is there a trick to inserting the code? This is how I entered it.

tblDiskdrives.Freespace,
Ceiling(tblDiskdrives.Freespace / Power(10, 9)) As [Free (GB)],
tblDiskdrives.Size,
Ceiling(tblDiskdrives.Size / Power(10, 9)) As [Capacity (GB)],

[RCorbeil]

To get the current system's RAM in gigabytes, you should be able to report tblAssets.Memory / 1024.

For the hard drive capacity, here's what I use:

  Ceiling(tblDiskdrives.Size / Power(10, 9)) As [Capacity (GB)],
  Ceiling(tblDiskdrives.Freespace / Power(10, 9)) As [Free (GB)],

That's in gigabytes as marketers measure mass storage capacity (i.e. 10^9). If you want it in gibibytes, like you measure RAM, use 2^30 instead.