topic Re: replace found data with "fix" and no data with "NULL" in Splunk Search

replace found data with "fix" and no data with "NULL"

CharterBT — Mon, 07 Oct 2013 15:25:18 GMT

I'm trying to perform a search where if there is data found in a specific field , then I want the report to replace that data with the word "fix". Conversely, if the field is empty, then I want the word "NULL" to display in my results.

Every time I try it, it seems my NULL values are getting overwritten and all I'm getting are "fix" in the cs5 field.

Any help is appreciated. Thanks in advance!

Re: replace found data with "fix" and no data with "NULL"

lukejadamec — Mon, 07 Oct 2013 15:29:37 GMT

Are you using
| fillnull value=NULL fieldname |
for your 'Null' results?
What are you using for you 'fix' results?

Re: replace found data with "fix" and no data with "NULL"

lukejadamec — Mon, 07 Oct 2013 15:38:08 GMT

Try using

| fillnull value=NULL cs5 |

for your 'Null' results?

And,

| eval cs5 = case(cs5="NULL","NULL",1=1,"fix") |

for your 'fix' replacement. Treat text as case sensitive.

Re: replace found data with "fix" and no data with "NULL"

CharterBT — Mon, 07 Oct 2013 15:38:19 GMT

Yes for the fillnull value.

I've tried to just use
replace [*] with [fix], but now it's giving me a search error.

I'm very new to Splunk, but here's the (latest version of the) part of the query I'm trying to run.

| fillnull value=NULL cs5 | replace [*] with [fix] cs5 |

I know that's probably the reason that all I was getting were "fix" results because the wildcard is catching everything. I'm not sure how to tell Splunk to differentiate between found data and NULL. And I need a null value so that I can get Splunk to count the instances of null.

Re: replace found data with "fix" and no data with "NULL"

CharterBT — Mon, 07 Oct 2013 15:46:58 GMT

Sorry, I was probably being unclear.

The eval fieldname query you suggested didn't replace any found data with the word "fix".

The fieldname that I'm focusing on could capture any combination of letters or numbers - if there's data in the field, I need to replace it with the word "fix". I don't need to retain the data, I just need a count.

If there's no data, I need the word "null" in that field. Then I can get a count of those as well.

This seems like it should be an easy query, but then again, these really basic queries can sometimes be harder than I think.

Re: replace found data with "fix" and no data with "NULL"

lukejadamec — Mon, 07 Oct 2013 15:58:40 GMT

Try case instead. The 1=1 is a default true that should match all non-NULL values. The NULL values will not make it to the end of the statement, so they should be ok.

Re: replace found data with "fix" and no data with "NULL"

alacercogitatus — Mon, 07 Oct 2013 16:04:34 GMT

Easy enough. So first, fill in your nulls with a value to check. Then in your stats, match on those that are null, and those that aren't null.

your_search | fillnull value="nullify" FIELDNAME | stats count(eval(searchmatch("FIELDNAME='nullify'"))) AS FOUND_NULL count(eval(searchmatch("FIELDNAME!='nullify'"))) AS FOUND_DATA

EDIT: updated searchmatch to make sure you are matching on the field. Per docs, searchmatch argument is a search string.

Re: replace found data with "fix" and no data with "NULL"

dwaddle — Mon, 07 Oct 2013 16:18:19 GMT

There's probably another way of doing it too...

| rex mode=sed field=foo "s/^.+$/fix/"
| eval foo=coalesce(foo,"NULL")

Re: replace found data with "fix" and no data with "NULL"

CharterBT — Mon, 07 Oct 2013 18:20:03 GMT

OK, I realize that in your first example, I change fieldname to actual name of the field (in my case, cs5). Do I change the word "case" in your second example? I ask because I ran it "as-is" for the second example, and I still was unable to get results that group into the two types I need.

Re: replace found data with "fix" and no data with "NULL"

CharterBT — Mon, 07 Oct 2013 18:25:37 GMT

This is the closest I've gotten to the results I need, because it created two columns labeled "Found Null" and "Found Data". But it grouped all the results under Found Data, and my previous queries have 70%+ of my results have null. I replaced FIELDNAME with the name of the field I'm wanting to change/count. What other part of this example do I have to change for this to work? I'm not familiar with searchmatch.

Re: replace found data with "fix" and no data with "NULL"

alacercogitatus — Mon, 07 Oct 2013 18:27:11 GMT

Can you post the actual fieldname, and the search you just tried?

Re: replace found data with "fix" and no data with "NULL"

lukejadamec — Mon, 07 Oct 2013 18:37:23 GMT

Sorry, yes the fieldname should be cs5, and you need to add quotes to the text in the case statement. No quotes around numbers or field names. I updated the answer.

Re: replace found data with "fix" and no data with "NULL"

CharterBT — Mon, 07 Oct 2013 19:05:29 GMT

BINGO! That did it! Thanks for your help and teaching me a new trick in Splunk, too!

Re: replace found data with "fix" and no data with "NULL"

CharterBT — Mon, 07 Oct 2013 19:07:54 GMT

lukejadamec got it to work for me, but thanks for all of your help as well! Have a great day!