topic Re: How to globally replace a value in any of the fields. in Splunk Search

How to globally replace a value in any of the fields.

jiaqya — Fri, 28 Feb 2020 17:36:34 GMT

i have a output where i have 0 in random columns.
i would like these 0's to be replaced with any text for reporting...

is it possible to replace 0 in any field ? ex output below

Jan2019 Feb2019 Mar2019 Apr2019
0 1 8 10
1 20 3 40
9 1 0 4

the above should change to
Jan2019 Feb2019 Mar2019 Apr2019
NA 1 8 10
1 20 3 40
9 1 NA 4

there are also 0's in other cells but they are not =0, but 10 ,40 , 20 etc...
but only =0 should be replaced, thanks for the response in advance..

Re: How to globally replace a value in any of the fields.

richgalloway — Fri, 28 Feb 2020 18:17:18 GMT

Have you tried | replace 0 with "NA" | ?

Re: How to globally replace a value in any of the fields.

jiaqya — Mon, 02 Mar 2020 03:46:07 GMT

This will replace the other 0 also to NA, like in 20,40 etc... and the column names are dynamic...

Re: How to globally replace a value in any of the fields.

manjunathmeti — Mon, 02 Mar 2020 05:21:12 GMT

hi @jiaqya,

Try this:

| foreach Jan2019,Feb2019,Mar2019,Apr2019 [ eval <<FIELD>>=if(<<FIELD>>==0, "NA", <<FIELD>>)]

If the interested fields contains same string in their names:

| foreach *2019 [ eval <<FIELD>>=if(<<FIELD>>==0, "NA", <<FIELD>>)]

Sample query:

| makeresults 
| eval _raw="Jan2019,Feb2019,Mar2019,Apr2019
0,1,8,10
1,20,3,40
9,1,0,4" 
| multikv forceheader=1 
| fields - _time,_raw,linecount 
| foreach Jan2019,Feb2019,Mar2019,Apr2019 
    [ eval <<FIELD>>=if(<<FIELD>>==0, "NA", <<FIELD>>)]

Re: How to globally replace a value in any of the fields.

anmolpatel — Mon, 02 Mar 2020 05:59:00 GMT

richgalloway's solution will work. replace will not modify values like in 20, 40 etc. That will only happen if you specify |replace *0 with "NA"

Re: How to globally replace a value in any of the fields.

jiaqya — Mon, 02 Mar 2020 06:58:53 GMT

Right, i was trying rex before and it was replacing 0 as NA everywhere it found.

with replace 0 with "NA" , it is only replacing the 0 , and not the 20/40
thanks, this solves my problem.

john.

Re: How to globally replace a value in any of the fields.

jiaqya — Mon, 02 Mar 2020 06:59:34 GMT

i do have my solution by using replace 0 as NA