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,765  
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 Email attachments
by  krejar   Go to last post Go to first unread
Last post: 8/15/2019 10:15:07 PM(UTC)
Lansweeper HP Printer not showing correctly
by  Fathom  
Go to last post Go to first unread
Last post: 8/15/2019 4:14:55 PM(UTC)
Lansweeper Install LsAgent on Mac
by  Jonnym   Go to last post Go to first unread
Last post: 8/14/2019 10:14:08 AM(UTC)
Lansweeper Asset State Question
by  rtaylor  
Go to last post Go to first unread
Last post: 8/13/2019 5:53:24 PM(UTC)
Lansweeper LSAgent Version
by  roberto.m  
Go to last post Go to first unread
Last post: 8/13/2019 3:33:50 PM(UTC)
Lansweeper Lansweeper and snmp v3
by  Justin5135   Go to last post Go to first unread
Last post: 8/12/2019 7:07:22 PM(UTC)
Lansweeper Hikvision CCTV Cameras SNMP scan
by  thejduk  
Go to last post Go to first unread
Last post: 8/12/2019 2:34:05 PM(UTC)