Purge SNMP switch scan records

[gderrick]

We have Lansweeper scanning our HP Comware switches via SNMP v3 that is working great but port connectivity information is historical not reflecting valid at last scan. We have computers that move locations showing up on different switches and ports within a single switch meaning that we get multiple references to that asset with us having to carefully check the Last Seen field to determine what is correct. It would be nice to have a selection to purge historical info so that the next scan will reflect current connections.

Thanks

[crashff]

I know this is a very old topic, but I have recently discovered something important to the resolution of it.

I have manually flushed out tblSNMPAssetMac repeatedly, and keep getting wrong pc's identified on our switches, even with a complete rescan.

I ended up doing a mac / asset name comparison between three tables: tblSNMPAssetMac, tblAssetMacAddress, and tbAssets

Here's the cross-reference I did:

Select Top 1000000 tblAssets.AssetUnique,
tblAssets.IPAddress,
tblAssets.AssetName,
tblSNMPAssetMac.AssetMacAddress As [Reported Mac],
tblAssetMacAddress.Mac As [Actual MAC],
tblSNMPAssetMac.SNMPMacID,
tblSNMPAssetMac.Lastchanged
From tblSNMPAssetMac
Inner Join tblAssets On tblSNMPAssetMac.AssetMacAddress = tblAssets.Mac
Inner Join tblAssetMacAddress
On tblAssets.AssetID = tblAssetMacAddress.AssetID

I discovered that tblAssetMacAddress had multiple devices listed with the same MAC address, which did not belong to any of the devices listed. This was causing the SNMP scan to map to the first device in the list with bad MAC addresses.

By purging BOTH tblAssetMacAddress, and tblSNMPAssetMac, and then rescanning all assets I was able to get a clean and accurate snmp mapping on my switches for the first time in 2+ years.

In SQLCE, under Configuration -> Database Scripts:
Run

Delete * from tblAssetMacAddress
Delete * from tblSNMPAssetMac

Rescan all assets.

[bbeavis]

I would like to see a function similar to the AD clean up. First, perhaps a historical log like use last logon or software changes. Second, a setting for age of port last scanned. So, if the setting was 30 days, then switch info older than that gets purged.

I realize I can use sql to script this outside of lansweeper (and I probably will), but it would be nice for this to be built into the interface.

[crashff]

I'm using the stock SQLCE install.

[sukaitsu]

Oh are you running SQL Compact database or SQL Server edition?

Thank you,

Jeffrey

[crashff]

I just tried the query and got an error.

- There was an error parsing the query. [ Token line number = 3,Token line offset = 1,Token in error = FROM ]

[sukaitsu]

I tested this out on our environment and it seems to work alright.

Delete tblSNMPAssetMac
FROM tblSNMPAssetMac
Inner Join tblSNMPAssetMac As t2 On tblSNMPAssetMac.AssetMacAddress = t2.AssetMacAddress
And tblSNMPAssetMac.LastSeen < t2.LastSeen And tblSNMPAssetMac.AssetMacAddress = t2.AssetMacAddress

Thank you,

Jeffrey

[crashff]

I may have a set of SQL queries worked out that will help with this if anyone wants to try them.

This should work in the report builder to find all the old SNMP entries:

Select Distinct Top 1000000 t1.*
From tblSNMPAssetMac As t1
  Inner Join tblSNMPAssetMac As t2 On t1.AssetMacAddress = t2.AssetMacAddress
    And t1.LastSeen < t2.LastSeen And t1.AssetMacAddress = t2.AssetMacAddress

Here's what I use to find the duplicates (including the currently active port):

Select Distinct Top 1000000 t1.*
From tblSNMPAssetMac As t1
  Inner Join tblSNMPAssetMac As t2 On t1.AssetMacAddress = t2.AssetMacAddress
    And t1.SNMPMacID <> t2.SNMPMacID And t1.AssetMacAddress = t2.AssetMacAddress

I'm not sure how to turn these into delete statements to erase only the old records.

[crashff]

I think a better way to implement the historical log would be to add it as a page under the Switch Asset. When a mac address is detected, but the port number has changed, write the old port to the log, but only display the currently used port on the main page.

This gives the users an easy to sort log that only shows what was moving around, and keeps the visual display current for quick reference. The way it is currently implemented, if two machines swap ports, the historical data isn't there anymore because a new machine is on the prior port, but there's no record of the port swap.

[Susan_A]

I would recommend looking at the dates next to the port connections to determine when those assets were detected. Alternatively, you could:

• Build a report that only lists connections detected during the last switch scan.
  
• Delete the port connection data from the tblSNMPAssetMac database table (i.e. with a script) and rescan the switch afterwards. This is not something we can provide instructions or support for however.

Listing historical port connections is not a feature we'll get rid of entirely, as many customers actually requested that this be implemented. We may "gray out" old connections in a future version however, to make it clearer that those are old connections.