1. PDQ Deploy & Inventory Help Center
  2. Community
  3. PDQ Inventory
 

Important Notice: On February 29th, this community was put into read-only mode. All existing posts will remain but customers are unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

SQL Reports of Memory Total Capacity

Avatar
matthew.knight

I've run the following report which returns a row for each memory module installed in a computer.

select
	c.Name as "Computer Name",
	m.Capacity,
	m.DeviceLocator,
	m.Speed,
	m.SerialNumber
from Computers c
join MemoryModules m on c.ComputerId = m.ComputerId
order by c.Name,m.DeviceLocator asc

Returns, as an example:

H<system_name>01	4294967296	A1_DIMM1	1600	3545E3D0
H<system_name>01	4294967296	A1_DIMM3	1600	2F899163
H<system_name>22	2147483648	ChannelA-DIMM1	1333	26110743
H<system_name>22	2147483648	ChannelB-DIMM1	1333	2651073C
H<system_name>12	2147483648	ChannelA-DIMM1	1333	DF8C2321
H<system_name>12	4294967296	ChannelB-DIMM0	1333	3ABFB0D1
H<system_name>12	2147483648	ChannelB-DIMM1	1333	DF8C232D
H<system_name>13	2147483648	ChannelA-DIMM1	1333	938EFDCE
H<system_name>13	2147483648	ChannelB-DIMM1	1333	938EFDD6

But I'm only interested in total capacity of each system and ultimately want to know which systems have less than 8GB of RAM installed. So I modify my SQL as follows:

select
	c.Name as "Computer Name",
	sum(m.Capacity) as "Installed Memory"
from Computers c
join MemoryModules m on c.ComputerId = m.ComputerId
order by c.Name asc

But this only returns one computer name (computer I'm running the report from) with Capacity listed as 1161788653568. It has 32GB installed. Any thoughts on how to get the answer I'm after for all systems in my inventory?

1

Comments

4 comments
Date Votes
  • Avatar
    lnichols

    Matthew,

    Is there a particular need for this to be a SQL report instead of a basic report? You can easily pull this info from a basic report with the "Computer Memory" field.

    1
  • Avatar
    matthew.knight

    When I use the basic report, I get multiple systems that show as having 0KB of memory. Obviously this doesn't give me what I'm looking for so I looked to the SQL report.

    1
  • Avatar
    pdq

    To avoid complicating this too much, I'd agree with Luke that a basic report is the best way to accomplish this. Just add a greater than filter as well to avoid pulling in results that don't yet have any data in that column and subsequently show 0KB.

    1
  • Avatar
    matthew.knight

    So I didn't realize that the 0KB were telling me that those systems didn't have any information for the field. I guess I assumed that that field would appear blank if it didn't have the info yet. Thanks for your help.

    1