cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
polar
Engaged Sweeper II
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
4 REPLIES 4
sandroriz
Engaged Sweeper
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
Engaged Sweeper II
Much appreciated. Mounted the report and informed my Manager so it can help with planning upgrades.
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
Lansweeper Employee
Lansweeper Employee
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.