Splunk Search

Replaceing NULL string for null()

jdanij
Path Finder

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

0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

NOT TESTED

Have you tried this instead?

SEDCMD-NullStringtoNull = s/NULL//g

View solution in original post

woodcock
Esteemed Legend

Like this:

 SEDCMD-NullStringtoNull = s/NULL//g

Then test like this:

... | where isnull(nullfield)
0 Karma

javiergn
SplunkTrust
SplunkTrust

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

javiergn
SplunkTrust
SplunkTrust

NOT TESTED

Have you tried this instead?

SEDCMD-NullStringtoNull = s/NULL//g

jdanij
Path Finder

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

what about?

| where isNull(nullfield)

jdanij
Path Finder

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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. 
0 Karma

javiergn
SplunkTrust
SplunkTrust

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

0 Karma

davebrooking
Contributor

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

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>