topic Re: Replaceing NULL string for null() in Splunk Search

Replaceing NULL string for null()

jdanij — Tue, 09 Aug 2016 06:47:42 GMT

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!!

Re: Replaceing NULL string for null()

javiergn — Tue, 09 Aug 2016 08:14:06 GMT

NOT TESTED

Have you tried this instead?

SEDCMD-NullStringtoNull = s/NULL//g

Re: Replaceing NULL string for null()

jdanij — Tue, 09 Aug 2016 09:49:32 GMT

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()...

Re: Replaceing NULL string for null()

davebrooking — Tue, 09 Aug 2016 11:16:29 GMT

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

Re: Replaceing NULL string for null()

javiergn — Tue, 09 Aug 2016 12:41:16 GMT

what about?

| where isNull(nullfield)

Re: Replaceing NULL string for null()

jdanij — Tue, 09 Aug 2016 12:43:50 GMT

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

Re: Replaceing NULL string for null()

javiergn — Tue, 09 Aug 2016 14:23:36 GMT

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.

Re: Replaceing NULL string for null()

javiergn — Tue, 09 Aug 2016 14:34:18 GMT

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

Re: Replaceing NULL string for null()

woodcock — Tue, 09 Aug 2016 15:57:07 GMT

Like this:

 SEDCMD-NullStringtoNull = s/NULL//g

Then test like this:

... | where isnull(nullfield)

Re: Replaceing NULL string for null()

javiergn — Thu, 01 Sep 2016 07:32:06 GMT

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.

Re: Replaceing NULL string for null()

woodcock — Thu, 01 Sep 2016 16:48:42 GMT

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.

Re: Replaceing NULL string for null()

woodcock — Thu, 01 Sep 2016 17:20:11 GMT

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.