This comprehensive RAM usage report furnishes IT administrators with actionable insights for targeted memory upgrades. By displaying average memory consumption alongside available memory slots and form factors, it is possible to swiftly identify which computers have both the physical capacity and the performance need for additional RAM. Systems demonstrating persistently high memory utilisation (surpassing 80%) in conjunction with brief uptime periods are likely to signify users encountering performance issues of such severity that frequent reboots are necessary, thus rendering them prime candidates for memory upgrades. The report's comprehensive analysis of memory specifications ensures that the correct type of memory is ordered (DIMM/SODIMM) when upgrading, while the username field enables the prioritization of upgrades based on user roles and requirements.
Select Top (1000000) a.AssetID,
Min(a.AssetName) As AssetName,
Min(a.Username) As Username,
Min(a.Domain) As Domain,
Min(Coalesce(os.Image, [at].AssetTypeIcon10)) As icon,
Min(a.IPAddress) As IPAddress,
Case Min(a.Assettype)
When -1 Then Min(os.OSname)
When 11 Then Min(ls.OSRelease)
Else ''
End As OsName,
Round(Cast(Min(a.Memory) / 1024.0 As float), 2) As [RAM Size (GB)],
Round(Cast(Avg(pcsm.Value) As float), 0) As [RAM % Average],
Min(pcsm.Value) As [RAM % MinValue],
Max(pcsm.Value) As [RAM % Max Value],
Min(pma.MemoryDevices) As [Total Memory Slots],
Case
When Min(pma.MemoryDevices) Is Null Then 'Unknown'
Else Cast((Select Count(Distinct tblPhysicalMemory.DeviceLocator)
From tblPhysicalMemory
Where tblPhysicalMemory.AssetID = a.AssetID) As VARCHAR)
End As [Used Memory Slots],
Case
When Min(pma.MemoryDevices) Is Null Then 'Unknown'
Else Cast(Min(pma.MemoryDevices) - (Select Count(Distinct
tblPhysicalMemory.DeviceLocator) From tblPhysicalMemory
Where tblPhysicalMemory.AssetID = a.AssetID) As VARCHAR)
End As [Available Memory Slots],
Case Min(pm.FormFactor)
When 0 Then 'Unknown'
When 1 Then 'Other'
When 2 Then 'SIP'
When 3 Then 'DIP'
When 4 Then 'ZIP'
When 5 Then 'SOJ'
When 6 Then 'Proprietary'
When 7 Then 'SIMM'
When 8 Then 'DIMM'
When 9 Then 'TSOP'
When 10 Then 'PGA'
When 11 Then 'RIMM'
When 12 Then 'SODIMM'
When 13 Then 'SRIMM'
When 14 Then 'SMD'
When 15 Then 'SSMP'
When 16 Then 'QFP'
When 17 Then 'TQFP'
When 18 Then 'SOIC'
When 19 Then 'LCC'
When 20 Then 'PLCC'
When 21 Then 'BGA'
When 22 Then 'FPBGA'
When 23 Then 'LGA'
Else 'Unknown'
End As [Memory Form Factor],
Round(Cast(Min(a.Uptime) / (1440 * 60) As DECIMAL(10,2)),
2) As [Days Since Last Reboot],
Min(a.Lastseen) As [Last successful scan]
From tblAssets As a
Inner Join tblAssetCustom As ac On a.AssetID = ac.AssetID
Inner Join tsysAssetTypes As [at] On [at].AssetType = a.Assettype
Inner Join tsysIPLocations As ipl On ipl.LocationID = a.LocationID
Inner Join tblState On tblState.State = ac.State
Left Outer Join tsysOS As os On os.OScode = a.OScode
Left Outer Join tblLinuxSystem As ls On ls.AssetID = a.AssetID
Inner Join tblPerformanceCountersScan As pcs On pcs.AssetId = a.AssetID
Inner Join tblPerformanceCountersScanMetric As pcsm On
pcsm.PerformanceCountersScanId = pcs.Id
Inner Join tsysPerformanceCounterMetric As pcm On pcm.Id = pcsm.Metric
Left Outer Join tblPhysicalMemoryArray As pma On a.AssetID = pma.AssetID
Left Outer Join tblPhysicalMemory As pm On a.AssetID = pm.AssetID
Where pcm.Id = 4 And tblState.Statename = 'Active' And pcs.ScanDateTime >
GetDate() - 14
Group By a.AssetID,
pcm.Id,
a.Memory
Having Avg(pcsm.Value) > 80
Order By [Days Since Last Reboot],
[RAM % Average] Desc,
AssetName
