topic Re: Iterate over lookup table to perform replace on search field in Splunk Search

Iterate over lookup table to perform replace on search field

wanderson8 — Fri, 28 May 2021 19:00:21 GMT

I am trying to use a lookup table to perform a series of string replacements on a single field in a search result

The lookup table has two fields:

find_string, replace_string

(??? find_string may need to be a regex for this purpose ???)

Then, for every row/event in the search result, I need it to iterate over the lookup table and perform the following operation for a single field from the search results (call it search_field) :

| eval search_field = replace(search_field, find_string, replace_string)

The search_field mutations should be cumulative within each search row/event. In other words, the return value from the replace function will become the input to the next iteration, until every entry in the lookup table has been iterated over. Then it moves onto the next row/event and starts over with the original value of search_field at the start of the lookup table, ETC...

I have tried many different approaches to this, with no success. Apparently unlike SQL, subsearches in Splunk are unable to access fields from the outer search. (???) I have also had no success with the map command. This seems like a fairly basic operation in most programming languages, and I think it would be even be do-able in SQL. Is it even possible to do this with Splunk?

Re: Iterate over lookup table to perform replace on search field

ITWhisperer — Fri, 28 May 2021 21:10:09 GMT

It may be possible depending on how many rows you have in you lookup table. Essentially, one approach might be to create a field for each row then use foreach to iterate over the fields.

Re: Iterate over lookup table to perform replace on search field

wanderson8 — Fri, 28 May 2021 22:42:51 GMT

If I'm going to do that I might as well just hard-code the search/replace strings in a series of eval replace commands, rather than do it as a lookup table.

My idea here is that I want the search/replace iterations to be dynamic based on the contents of the lookup table. I expect the table will grow over time.

If Splunk can't do this (or at least without some major hacky work-arounds) then the hard-coding might just be the way to go, even though it is far from an ideal approach... I'm fairly new to Splunk, so maybe there is something I'm missing here?

Re: Iterate over lookup table to perform replace on search field

ITWhisperer — Sat, 29 May 2021 06:15:34 GMT

Fair enough, I wasn't clear when I said create a field, I meant dynamically create a field (from the rows in the lookup table)

| makeresults count=10 | streamstats count as row | eval _raw="data for user".mvindex(split("ABCDEFGHIJ",""),(row-1)%10)." is ".(row*123) ``` creates dummy data ``` | append [| makeresults count=5 | fields - _time | streamstats count as user | eval match="user".mvindex(split("ABCDEFGHIJ",""),(10-user)%10)." is \\d+" | eval replacement="user".mvindex(split("ABCDEFGHIJ",""),(10-user)%10)." is XXX" ``` creates lookup data ``` ``` count the rows in the lookup table ``` | streamstats count as row ``` convert to names for columns ``` | eval column="replace_".row ``` create mvfield with match and replacement strings ``` | eval concat=mvappend(match,replacement) ``` reduce fields to name and mvfield ``` | fields column concat ``` transpose rows to columns ``` | transpose 0 header_field=column ``` drop column field ``` | fields - column] ``` reverse events so that appended event is at the top ``` | reverse ``` copy replacement strings to all rows ``` | filldown replace_* ``` ignore appended row ``` | where isnotnull(_raw) ``` restore order of events ``` | reverse ``` copy the field being changed - not necessary, merely to show the change ``` | eval original=_raw ``` for each replacement ``` | foreach replace_* ``` perform the replacement on the field ``` [| eval _raw=replace(_raw,mvindex(<<FIELD>>,0),mvindex(<<FIELD>>,1))]

Re: Iterate over lookup table to perform replace on search field

wanderson8 — Tue, 01 Jun 2021 15:17:08 GMT

THANK YOU SO MUCH. This works!

It seems a little hacky and inefficient, but that's more a limitation of the Splunk query language than anything! Only wish there was a more efficient and sensible way to do this 😕 ... guess I could start looking at making custom apps!