Notification

Icon
Error

RAM status (color-coded)

Posted: Tuesday, September 25, 2018 9:31:03 AM(UTC)
polar

polar

Member Original PosterPosts: 4
6
Like
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:
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
Esben.D
#1Esben.D Member Administration Posts: 1,632  
posted: 10/1/2018 4:48:15 PM(UTC)
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.
HappyHeathen
#2HappyHeathen Member Posts: 9  
posted: 11/9/2018 7:39:13 PM(UTC)
Much appreciated. Mounted the report and informed my Manager so it can help with planning upgrades.
sandroriz
#3sandroriz Member Posts: 3  
posted: 11/12/2018 2:47:16 PM(UTC)
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.
Rmendez2018
#4Rmendez2018 Member Posts: 1  
posted: 11/12/2018 8:32:55 PM(UTC)
Originally Posted by: HappyHeathen Go to Quoted Post
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?

Active Discussions

Lansweeper Windows 7 Assets, their user, and users manager
by  Jeff Hilmo   Go to last post Go to first unread
Last post: Today at 6:51:55 PM(UTC)
Lansweeper Firefox 67 Critical Security Issue
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 11:40:51 AM(UTC)
Report Center BIOS version check
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 1:22:28 PM(UTC)
Lansweeper report in defined period
by  CHIREC  
Go to last post Go to first unread
Last post: Yesterday at 10:46:34 AM(UTC)
Lansweeper Microsoft Patch Tuesday Report - May 2019
by  Hendrik.VE  
Go to last post Go to first unread
Last post: 5/21/2019 8:32:41 AM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  Esben.D   Go to last post Go to first unread
Last post: 5/21/2019 8:25:30 AM(UTC)
Lansweeper Pc's with SSD
by  Rick I  
Go to last post Go to first unread
Last post: 5/17/2019 6:21:48 PM(UTC)