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 Fonts
by  TKinnie   Go to last post Go to first unread
Last post: Today at 4:00:40 PM(UTC)
Action Schedule Reboot using AT and psshutdown with time input
by  spatchE  
Go to last post Go to first unread
Last post: 5/21/2019 5:35:46 PM(UTC)
Action Open users local Temp directory
by  studerje   Go to last post Go to first unread
Last post: 5/14/2019 7:24:24 PM(UTC)
Lansweeper CMD- CFI MS Update remover 2
by  Rodney Stowell   Go to last post Go to first unread
Last post: 4/18/2019 6:55:01 PM(UTC)
Action Skype User
by  cctech  
Go to last post Go to first unread
Last post: 4/18/2019 4:14:54 PM(UTC)
Action Email user
by  cctech   Go to last post Go to first unread
Last post: 4/18/2019 3:48:50 PM(UTC)
Lansweeper RDP to the asset
by  AZHockeyNut  
Go to last post Go to first unread
Last post: 4/9/2019 6:31:48 PM(UTC)