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
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
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
Four years later and this post is still helping people. Thanks very much, friend!