Splunk Search

Iterate over lookup table to perform replace on search field

wanderson8
Engager

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?

Labels (3)
0 Karma
1 Solution

ITWhisperer
Legend

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))]

View solution in original post

0 Karma

ITWhisperer
Legend

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.

0 Karma

wanderson8
Engager

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?

0 Karma

ITWhisperer
Legend

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))]

View solution in original post

0 Karma

wanderson8
Engager

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!

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!