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

[RahulMisra1]

How i update the test_MID_IP.csv  with the output IP, so that next time it runs with updated list

index=abc IP!="10.*" [| inputlookup ip_tracking.csv | rename test_DATA AS MID | format ] | lookup test_MID_IP.csv test_IP as IP OUTPUT test_IP | eval match=if('IP'== test_IP, "yes", "no")| search match=no | stats count by IP

[RahulMisra1]

I am running this. how can i append the IP form below query to test.csv

index=<abc>[| inputlookup ip_tracking.csv | rename FDS AS MID | format ] | lookup test.csv test_IP as IP OUTPUT test_IP | eval match=if('IP'== test_IP, "yes", "no")| search match=no | stats count by IP

[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!