
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-15-2015 09:07 PM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-16-2015 04:32 PM
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]
3 REPLIES 3

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-16-2015 04:32 PM
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]

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-16-2015 03:37 PM
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)],
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)],
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-15-2015 10:15 PM
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:
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.
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.
