Replaceing NULL string for null()

[jdanij]

Hi,

I'm trying to reuse an old app for a new environment and, of course, data and fields similar but different, so adapting this part is where the big efforts come. It's 90% done already but, however, I'm stucked in this point. I've got semicolon-separated data, that makes it really simple to parse. The problem is that, fields with no data contain the string "NULL". This doesn't fit at all my needs. What I need is to convert these NULL strings into null-valued fields, just the same if I do:

...| eval myNullField = null()

I now that I cannot get it using null() into a SEDCMD, but just to explain this better, this shouold be perfect:

SEDCMD-NullStringtoNull = s/NULL/null()/g

I don't know if null() returns and hex code that means null for Splunk... Using that code into a SEDCMD could do the trick.

Of course, an easy option could be rewriting that fields with SPL, but that implies modifying each query, and that is my very last option.

Thanks a lot!!

[woodcock]

Like this:

 SEDCMD-NullStringtoNull = s/NULL//g

Then test like this:

... | where isnull(nullfield)

[javiergn]

Hi @jdanij, out of curiosity, what's the difference between this answer and mine apart from the fact I answered it hours before and provided a lot more detail, answered all your questions, and spent a decent amount of time there.

Just curious to know why you didn't mark mine instead.

Also curious to know why @woodcock posted this without having read mine first as it had already been answered in exactly the same way.

[woodcock]

Also, MANY times questions get flagged for moderation and when that happens, all answers are pended (not published) until the moderation is cleared and then all the answers come out at the same time. This is another reason for every similar answers and has happened to me many times.

[woodcock]

I open a ton of tabs early in the morning and in my down-time I answer and then close. I guess I should refresh first.

[javiergn · Accepted solution]

NOT TESTED

Have you tried this instead?

SEDCMD-NullStringtoNull = s/NULL//g

[jdanij]

Yes, that was my first option, but I get no results when I look for that fields like this:
... | where nullfield=null()

That makes me think that empty-string is not the same as null()...

[javiergn]

what about?

| where isNull(nullfield)

[jdanij]

yes, this is working!!
But I'm in doubt... what's the difference between ... | where field1 = null() and ... | where isnull(field1)??

[javiergn]

null() is a function that returns a NULL value and you can use that as part of eval to assign a NULL value to your field.

Where is expecting a boolean result (true, false) and should throw the following error when you do | where field = null():

Error in 'where' command: Typechecking failed. The '==' operator received different types. 

[javiergn]

By the way, if you are happy with the answer please don't forget to mark it as answered so that others can benefit from it in future

[davebrooking]

Could you set a calculated field in props.conf for the field(s) in question, maybe something like

EVAL-field1 = if(field1="NULL",null(),field1)

Then in the search you could use

... | where if(isnull(field1),1,0)=1

My instance of Splunk complains about datatype mismatch when I try ... | where field1=null(), but that may just be down to my sample data.

Dave