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

[Mister_Nobody]

Simple when disk was removed report:

Select Top 1000000 tblAssets.assetid,
  tblAssets.Domain,
  tblAssets.AssetName,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblFloppyHist.Model,
  tblFloppyHist.SerialNumber,
  tblFloppyHist.FirmwareRevision,
  tblFloppyHist.Size,
  Max(tblFloppyHist.Lastchanged) max_lastchanged,
  Case tblFloppyHist.Action
    When 1 Then 'Added'
    When 2 Then 'Removed'
    When 3 Then 'Updated'
  End As Action
From tblFloppyHist
  Inner Join tblAssets On tblAssets.AssetID = tblFloppyHist.AssetID And
      tblFloppyHist.interfacetype <> 'USB' And tblFloppyHist.size > 0 And
      tblFloppyHist.Action = 2 And tblFloppyHist.Model Not Like '%Virtual%'
  Left Join tblfloppy t On tblFloppyHist.assetid = t.assetid And
      t.SerialNumber = tblFloppyHist.SerialNumber
Where t.AssetID Is Null
Group By tblAssets.assetid,
  tblAssets.Domain,
  tblAssets.AssetName,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblFloppyHist.Model,
  tblFloppyHist.SerialNumber,
  tblFloppyHist.Size,
  tblFloppyHist.Action,
  tblFloppyHist.FirmwareRevision
Order By max_lastchanged Desc

*Only Windows
**USB and Virtual disks are filtered

[Mister_Nobody]

Simple when memory was removed report:

Select Top 1000000 tblAssets.assetid,
  tblAssets.Domain,
  tblAssets.AssetName,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblPhysicalMemoryHist.Manufacturer,
  tblPhysicalMemoryHist.SerialNumber,
  tblPhysicalMemoryHist.PartNumber,
  tblPhysicalMemoryHist.Capacity,
  Max(tblPhysicalMemoryHist.Lastchanged) max_lastchanged,
  Case tblPhysicalMemoryHist.Action
    When 1 Then 'Added'
    When 2 Then 'Removed'
    When 3 Then 'Updated'
  End As Action
From tblPhysicalMemoryHist
  Inner Join tblAssets On tblAssets.AssetID = tblPhysicalMemoryHist.AssetID And
      tblPhysicalMemoryHist.Action = 2 And tblAssets.oscode Not Like '%s'
  Left Join tblPhysicalMemory t On tblPhysicalMemoryHist.assetid = t.assetid And
      t.SerialNumber = tblPhysicalMemoryHist.SerialNumber
Where t.AssetID Is Null
Group By tblAssets.assetid,
  tblAssets.Domain,
  tblAssets.AssetName,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblPhysicalMemoryHist.Manufacturer,
  tblPhysicalMemoryHist.SerialNumber,
  tblPhysicalMemoryHist.PartNumber,
  tblPhysicalMemoryHist.Capacity,
  tblPhysicalMemoryHist.Action
Order By max_lastchanged Desc

*Only Windows
**Windows Server is filtered

[Obi_1_Cinobi]

Hello there!

Very nice report! 😎