Splunk Search

How do I update a lookup file with updated information

Contributor

I have a lookup file that contains two columns, ip and mac. I want to update this file daily by running a query that catches when either a new device is added or an existing device is moved. My query is

index=syslog logdesc="neighbor table change" vendor_action="add"
| regex srcip = "(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"
| stats latest(srcip) BY mac
| rename "latest(srcip)" AS srcip
| fields mac srcip 
| lookup srcip_mac.csv mac OUTPUTNEW srcip 
| outputlookup append=true srcip_mac.csv 

What happens is that I end up with a file that contains the updated data in a new line but the existing items are duplicated. I end up with a file that is twice the size it needs to be.

Any help will be greatly appreciated.

Regards,
Scott

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Your query needs to read the existing lookup, read the new events, dedup the results, then write to the lookup file. Something like this:

index=syslog logdesc="neighbor table change" vendor_action="add"
| regex srcip = "(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})"
| stats latest(srcip) BY mac
| rename "latest(srcip)" AS srcip
| fields mac srcip
| intputlookup append=true srcip_mac.csv
| dedup srcip
| outputlookup srcip_mac.csv

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

SplunkTrust
SplunkTrust

Your query needs to read the existing lookup, read the new events, dedup the results, then write to the lookup file. Something like this:

index=syslog logdesc="neighbor table change" vendor_action="add"
| regex srcip = "(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})"
| stats latest(srcip) BY mac
| rename "latest(srcip)" AS srcip
| fields mac srcip
| intputlookup append=true srcip_mac.csv
| dedup srcip
| outputlookup srcip_mac.csv

---
If this reply helps you, an upvote would be appreciated.

View solution in original post