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]

Processor and motherboard last replace:

Select Top 1000000 tblBaseBoardhist.assetid,
  tblassets.assetname,
  tblBaseBoardhist.Manufacturer,
  tblBaseBoardhist.Product,
  Max(tblBaseBoardhist.Lastchanged) max_lastchanged,
  MB.Manufacturer As CurrentManufacturer,
  MB.Product As CurrentProduct
From tblBaseBoardhist
  Inner Join tblassets On tblassets.AssetID = tblBaseBoardhist.assetid
      And tblBaseBoardhist.Action = 2
  Left Join tblBaseBoard MB On tblBaseBoardhist.AssetID = MB.AssetID
Where tblBaseBoardhist.Product <> MB.Product
Group By tblBaseBoardhist.assetid,
  tblassets.assetname,
  tblBaseBoardhist.Manufacturer,
  tblBaseBoardhist.Product,
  MB.Manufacturer,
  MB.Product
Union
Select tblProcessorHist.AssetID,
  tblAssets.assetname,
  Case tblProcessorHist.Manufacturer
    When 'GenuineIntel' Then 'Intel'
    When 'AuthenticAMD' Then 'AMD'
    Else tblProcessorHist.Manufacturer
  End Manufacturer,
  tblProcessorHist.Name As Product,
  Max(tblProcessorHist.Lastchanged) max_lastchanged,
  Case t.Manufacturer
    When 'GenuineIntel' Then 'Intel'
    When 'AuthenticAMD' Then 'AMD'
    Else t.Manufacturer
  End CurrentManufacturer,
  t.name As CurrentProduct
From tblProcessorHist
  Inner Join tblAssets On tblAssets.AssetID = tblProcessorHist.AssetID
      And tblProcessorHist.Action = 2
  Left Join tblProcessor t On tblProcessorHist.assetid = t.assetid
Where tblProcessorHist.Name <> t.name And tblAssets.mac Not Like '00:50:56:%'
Group By tblProcessorHist.AssetID,
  tblAssets.assetname,
  tblProcessorHist.Manufacturer,
  tblProcessorHist.Name,
  t.Manufacturer,
  t.name

[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]

Disk Last Replace Report:

Select Top 1000000 tblAssets.assetid,
  tblAssets.Domain,
  tblAssets.AssetName,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblFloppyHist.model,
  tblFloppyHist.SerialNumber,
  tblFloppyHist.InterfaceType,
  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,
  tblFloppy.model As cur_model,
  tblFloppy.SerialNumber As cur_SN,
  tblFloppy.InterfaceType As cur_IT,
  tblFloppy.size As cur_size
From tblFloppyHist
  Inner Join tblAssets On tblAssets.AssetID = tblFloppyHist.AssetID
  Left Join tblFloppy t On tblFloppyHist.assetid = t.assetid And
      tblFloppyHist.SerialNumber = t.SerialNumber
  Left Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID And
      tblFloppy.size > 0 And tblFloppy.InterfaceType <> 'USB'
Where tblFloppyHist.InterfaceType <> 'USB' And tblFloppyHist.size > 0 And
  tblFloppy.model Not Like '%virtual%' And tblFloppyHist.Action = 2 And
  t.AssetID Is Null
Group By tblAssets.assetid,
  tblAssets.Domain,
  tblAssets.AssetName,
  tblAssets.Userdomain,
  tblAssets.Username,
  tblFloppyHist.model,
  tblFloppyHist.SerialNumber,
  tblFloppyHist.InterfaceType,
  tblFloppyHist.size,
  tblFloppyHist.Action,
  tblFloppy.model,
  tblFloppy.SerialNumber,
  tblFloppy.InterfaceType,
  tblFloppy.size
Order By max_lastchanged Desc

[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! 😎