IP History

[danielm]

I know there have been a few other report examples using tblNetworkHist , but when I've tried them they seem to show odd results. Mainly the same ip over and over again, which makes sense if you are scanning frequently and the IP never changes.
Does anyone have a good report to show IP history, mainly to find laptops that would go off the lan and show up on a specific VPN address range now and then. should I change the WAIT/REFRESH time for Network to '0' to make sure it updates?
I don't want to fill up my database will useless data but it would be nice to be able to capture the ip changes "if changed", although I dont need 10000 entries of the same ip 😉 really only a change history if captured.


This is the last report I tried , but I am open to any suggestions.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblNetwork.IPAddress As [current IP],
tsysIPLocations.IPLocation As [current IP location],
tblNetworkhist.IPAddress As [recent IP],
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNetworkhist On tblAssets.AssetID = tblNetworkhist.AssetID
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetworkhist.MACaddress = tblNetwork.MACaddress And
tblNetworkhist.IPAddress <> tblNetwork.IPAddress And tblAssetCustom.State = 1
And tblNetworkhist.IPEnabled = 1 And tblNetwork.IPEnabled = 1
Order By tblNetworkhist.Lastchanged Desc

[Susan_A · Accepted solution]

To list IP address changes, could you try the report below instead. If the computer used the same IP addresses multiple times, the report will only list the last time each IP was used.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress As [current IP],
  tsysIPLocations.IPLocation As [current IP location],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblNetworkHist.IPAddress As [IP change],
  Max(tblNetworkHist.Lastchanged) As [IP change date]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblNetworkHist On tblAssets.AssetID = tblNetworkHist.AssetID
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssetCustom.State = 1 And tblNetworkHist.IPEnabled = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblNetworkHist.IPAddress
Order By [IP change date] Desc