Memory Report

[pfenton]

This is what I have. It works except that since I added memory speed to the report it started listing each stick of ram. How can I get the report to just list one line for each computer.

Select Top 1000000 tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) As MaxCapacity,
tblAssets.Memory,
Cast(CorrectMemory.Used As numeric) As [Slots used],
tblPhysicalMemoryArray.MemoryDevices As [Slots available],
tblPhysicalMemoryArray.MemoryDevices - CorrectMemory.Used As [Slots free],
tblPhysicalMemory.Speed,
tblAssetCustom.Model
From tblAssets
Inner 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
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblPhysicalMemory On tblAssets.AssetID = tblPhysicalMemory.AssetID
Where tblPhysicalMemoryArray.[Use] = 3 And tblAssetCustom.State = 1
Order By [Slots free] Desc,
tblAssets.AssetName

[pfenton]

I think that depends on the system. I have some 1600 Mhz memory in computers that max out at 1333 Mhz and Lansweeper reports it as 1333 Mhz.

[fjca]

Hi,

Please be aware that the speed shown on that field/report is the MAXIMUM speed of the DIMM, it may not be the speed that is actually running right now.

So, let's say you have a server with four DIMM's, two DDR3 at 1333 Mhz and two at 1600 Mhz, and have a low end Xeon like a E5504, that runs at 800 Mhz. On that server, you will have two DIMM's with speed 1333 Mhz, two with 1600 Mhz, and the CPU is actually running them at 800 MHz...

[Esben_D · Accepted solution]

The easiest way to prevent identical duplicate rows is by using the DISTINCT function. This will only show unique rows. However, when using DISTINCT, all items in your Order By clause also need to be in your Select statement.

For you query this means you need to change the following:

Select Distinct Top 1000000 tsysOS.Image As Icon,
...

Additionally you either need to remove tblAssets.AssetName from Order By or add it under Select.