→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !
04-25-2024 05:48 AM - edited 04-25-2024 05:49 AM
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
05-15-2024 01:45 PM
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
05-08-2024 01:07 PM
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
10-01-2024 11:59 AM - edited 10-01-2024 11:59 AM
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
05-08-2024 01:05 PM
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
04-26-2024 01:36 PM
Hello there!
Very nice report! 😎
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now