How do I update a lookup file with updated information

[scottrunyon]

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

[richgalloway · Accepted solution]

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

[jmcnutt]

Four years later and this post is still helping people.  Thanks very much, friend!