Using history to keep track of data can add a lot of value when analyzing network changes. With businesses using static IP addresses for most services, VPN connections, servers and more, keeping track of when an IP address of an asset has changed can help with troubleshooting, maximizing security, and also post-incident analysis.
The report below will provide an overview of all IP address changes to enabled NICs, obviously, changes must take place before the report will provide data. You can read more about this use case in the Pro Tips blog post.
Before running the audit, history tracking must be enabled for the Network item in ScanningScanned Item Interval
IP History Report Query
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Version,
tblAssets.SP,
tblNetworkHist.Description As [NIC],
tblNetworkHist.IPAddress As [IP changed to],
tblNetworkHist.Lastchanged As [IP change date],
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblNetworkHist On tblAssets.AssetID = tblNetworkHist.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Where tblState.Statename = 'Active' And tblNetworkHist.IPEnabled = 1
Order By tblAssets.Domain,
tblAssets.AssetName
