RAM status (color-coded)

[polar]

Here's the modified version of "Memory: Available slots" report.
PCs are sorted ascending by current amount of installed memory and then descending by slots available, color coded with green if 8 or more GB of RAM is installed, yellow if >=4GB and <8GB, red if <4GB. Condition of having more than 0 slots free is modified so all PCs are visible. I hope someone finds this useful.

Code:

Select Top 1000000 tsysOS.Image As Icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) As MaxCapacity,
  CorrectMemory.Memory,
  Case
    When CorrectMemory.Memory < 4096 Then '#f7caca'
    When CorrectMemory.Memory >= 4096 And CorrectMemory.Memory < 8192 Then
      '#f7f0ca' Else '#d4f4be'
  End As backgroundcolor,
  Cast(CorrectMemory.Used As numeric) As [Slots  used],
  tblPhysicalMemoryArray.MemoryDevices As [Slots available],
  tblPhysicalMemoryArray.MemoryDevices - CorrectMemory.Used As [Slots free],
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Firstseen,
  tblAssets.Lastseen
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 And
    Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) > CorrectMemory.Memory
    And Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) >
    CorrectMemory.Memory
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblPhysicalMemoryArray.MemoryDevices - CorrectMemory.Used >= 0 And
  tblPhysicalMemoryArray.[Use] = 3 And tblAssetCustom.State = 1
Order By CorrectMemory.Memory,
  [Slots free] Desc,
  tblAssets.AssetName

[sandroriz]

This condition "And Ceiling(tblPhysicalMemoryArray.MaxCapacity / 1024) > CorrectMemory.Memory" in the HAVING clause is duplicated. Moreover, if I left -also alone-, in my database a quite larger number of Assets are excluded.

Another issue is that I had a couple of PC repeated (they have 2 records in the tblPhysicalMemory...very old assets, so maybe now the slots are triggered in different mode). However a DISTINCT on the top could be better.

At last one virtual PC (Oracle Virtual Box) was not extracted because it has no record in tblPhysicalMemory.

Hope this helps to someone.

[HappyHeathen]

Much appreciated. Mounted the report and informed my Manager so it can help with planning upgrades.

[Rmendez2018]

HappyHeathen wrote:
Much appreciated. Mounted the report and informed my Manager so it can help with planning upgrades.

What are the steps to create this new report?

[Esben_D]

Thanks you sharing this. It's interesting just to look at, even if you don't really need it at the moment.

I did notice that virtual machines seem to have 64 RAM slots (which I presume is normal as they are not physical machines), but it's still useful to have the color-coding based on the amount of RAM.