Splunk lookup using csv-keys as input and csv-values as output

[bhupalbobbadi]

I have event data as follows: a,b,",1,2,3,",c,d

And I have lookup table as follows

key,     value
1,       one
2,       two
3,       three
4,       four

I need the following output using lookup
a,b,",one,two,three,",c,d

note ",1,2,3," is not fixed in size.

Any help appreciated.

[DalJeanis · Accepted solution]

Try this...

| makeresults 
| eval field1="a" | eval field2="b" | eval  field3=",1,2,3," | eval field4="c" | eval field5="d"
| rename COMMENT as "The above enters your test data"

| rename COMMENT as "Deal with the leading comma, mark spots to split the data, split the data into separate records"
| rex mode=sed field=field3 "s/^,/((nothing)),/g s/,/,!!!!/g s/!!!!$//g"
| makemv delim="!!!!" field3
| mvexpand field3

| rename COMMENT as "Do the lookup, add a comma if found"
| lookup mylookup.csv field3 OUTPUT field3b
| eval field3=if(isnull(field3b),field3,field3b.",")
| fields - field3b
| rename COMMENT as "For run anywhere you can just delete the lookup to prove it works"

| rename COMMENT as "Recombine the records, join them, undo the fix for the leading comma, voila"
| mvcombine field3
| eval field3=mvjoin(field3,"!!!!")
| rex mode=sed field=field3 "s/\(\(nothing\)\),/,/g s/,!!!!/,/g"

[bhupalbobbadi]

Thanks tons. It worked as I wanted.