A colorful report for a not-so-fun situation: Memory tracking

[Mister_Nobody]

From time to time, RAM disappears into our computers. The reasons are various - contacts oxidized, chip burned out, stolen by employees who want to make a profit, or scheduled replacement.
We decided to develop a memory monitoring report.
We came up with an algorithm - if the size of the removed memory is larger than the size of the added memory, we write it in the report.
We also decided to play with coloring. Lansweeper supports font and background colors.
We thought for a long time about the algorithm of color selection, so that one computer could be different from another.
In the end, we decided to take the last three octets of MAC-address and turn them into RGB-code for color, also we took one of the algorithms for color inversion.
A lot of work had to be done with HEX to string conversion functions and vice versa.
The resulting report looks rather acidic, but it fulfills its function.

* You must to enable MEMORY history at Scanned Item Interval, also I recommend to set Refresh interval to 1.

Select Top 1000000 t.AssetID,
  t.Domain,
  t.AssetName,
  t.userdomain,
  t.username,
  t.Lastchanged,
  t.Action,
  t.DimmMB,
  t.memory As SummaryMemory,
  t.OSMemory As SummaryMemory_OSseems,
  t.MemoryName,
  t.Manufacturer As Mem_Manufacturer,
  t.PartNumber,
  t.SerialNumber,
  t.[Last successful scan],
  '#' + t.bgc As backgroundcolor,
  '#' + t.cr + t.cg + t.cb foregroundcolor
From (Select tblAssets.AssetID,
      tblAssets.Domain,
      tblAssets.AssetName,
      Replace(SubString(tblAssets.mac, Len(tblAssets.mac) - 7, 8), ':', '') bgc,
      Right(Convert(varchar(4),Convert(binary(1),Case
        When Convert(int,Convert(binary(1),SubString(tblAssets.mac,
          Len(tblAssets.mac) - 7, 2),2)) < 128 Then 255
        Else 0
      End),1), 2) cr,
      Right(Convert(varchar(4),Convert(binary(1),Case
        When Convert(int,Convert(binary(1),SubString(tblAssets.mac,
          Len(tblAssets.mac) - 4, 2),2)) < 128 Then 255
        Else 0
      End),1), 2) cg,
      Right(Convert(varchar(4),Convert(binary(1),Case
        When Convert(int,Convert(binary(1),SubString(tblAssets.mac,
          Len(tblAssets.mac) - 1, 2),2)) < 128 Then 255
        Else 0
      End),1), 2) cb,
      tblPhysicalMemoryHist.Lastchanged,
      Case tblPhysicalMemoryHist.Action
        When 1 Then 'Added'
        When 2 Then 'Removed'
      End As Action,
      Ceiling(tblPhysicalMemoryHist.Capacity / 1024 / 1024) As DimmMB,
      Ceiling(Sum(Case tblPhysicalMemoryHist.Action
        When 1 Then tblPhysicalMemoryHist.Capacity
        Else 0
      End) Over (Partition By tblAssets.AssetID) / 1024 / 1024) CapacityAdded,
      Ceiling(Sum(Case tblPhysicalMemoryHist.Action
        When 2 Then tblPhysicalMemoryHist.Capacity
        Else 0
      End) Over (Partition By tblAssets.AssetID) / 1024 / 1024) CapacityRemoved,
      TsysMemorytypes.MemoryName,
      tblAssets.Lastseen As [Last successful scan],
      tblPhysicalMemoryHist.Manufacturer,
      tblPhysicalMemoryHist.PartNumber,
      tblPhysicalMemoryHist.SerialNumber,
      tpm.memory,
      Ceiling(tos.TotalVisibleMemorySize / 1024) As OSMemory,
      tblAssets.Userdomain,
      tblAssets.Username
    From tblAssets
      Inner Join tblPhysicalMemoryHist On tblAssets.AssetID =
          tblPhysicalMemoryHist.AssetID
      Inner Join TsysMemorytypes On tblPhysicalMemoryHist.MemoryType =
          TsysMemorytypes.Memorytype
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
      Inner Join tblOperatingsystem tos On tblAssets.AssetID = tos.AssetID
      Left Join (Select itpm.Assetid,
          Ceiling(Sum(itpm.capacity) / 1024 / 1024) As memory
        From tblPhysicalMemory itpm
        Group By itpm.AssetID) tpm On tblAssets.AssetID = tpm.AssetID
    Where Not (tblPhysicalMemoryHist.Manufacturer In ('VMware Virtual RAM',
      'microsoft', 'microsoft corporation')) And tblAssetCustom.State = 1) t
Where t.CapacityAdded < t.CapacityRemoved
Order By t.Lastchanged Desc,
  t.AssetName

[Obi_1_Cinobi]

Hello there!

Very nice report! 😎