topic Re: How do I update a lookup file with updated information in Splunk Search

How do I update a lookup file with updated information

scottrunyon — Fri, 28 Jun 2019 19:31:55 GMT

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

Re: How do I update a lookup file with updated information

richgalloway — Wed, 30 Sep 2020 01:08:06 GMT

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

Re: How do I update a lookup file with updated information

jmcnutt — Wed, 12 Jul 2023 13:49:38 GMT

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

Re: How do I update a lookup file with updated information

RahulMisra1 — Thu, 13 Jun 2024 14:27:57 GMT

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

Re: How do I update a lookup file with updated information

RahulMisra1 — Fri, 14 Jun 2024 12:03:38 GMT

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