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
Options
Esben.D
#1Esben.D Member Administration Posts: 1,966  
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.
0
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.
0
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.
0
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?
0

Active Discussions

Lansweeper TPM scanning
by  gene@sca   Go to last post Go to first unread
Last post: Yesterday at 10:40:24 PM(UTC)
Lansweeper Bulk AssetLock set
by  tcooper   Go to last post Go to first unread
Last post: Yesterday at 6:24:15 PM(UTC)
Lansweeper Set AssetType based on Model?
by  tcooper   Go to last post Go to first unread
Last post: Yesterday at 6:16:57 PM(UTC)
Lansweeper Public IP Address
by  David Goodwin   Go to last post Go to first unread
Last post: Yesterday at 4:57:14 PM(UTC)
Lansweeper Exchange Scan in v7 and DAG
by  Peter.Riederer   Go to last post Go to first unread
Last post: Yesterday at 8:42:48 AM(UTC)
Lansweeper Asset Scanning - Change Types
by  DNuhija   Go to last post Go to first unread
Last post: 12/12/2019 5:40:47 PM(UTC)
Lansweeper Plundervolt
by  NWSF   Go to last post Go to first unread
Last post: 12/12/2019 5:10:14 PM(UTC)
Lansweeper MSI Package
by  Stefano Magri   Go to last post Go to first unread
Last post: 12/12/2019 3:34:16 PM(UTC)