topic Re: empty fields after a left join in Splunk Search

Why are there empty fields after a left join?

pierre_weg — Wed, 15 Mar 2023 16:13:01 GMT

Hello fellows!

I have a sourcetype called cmdb with a field called BIA to any src_host.

After this join

index=lab sourcetype=A | join type=left src_host [search index=lab sourcetype=cmdb]

Most of the src_host now figures with the BIA field, but some of them don't. It's OK, because they do not exist on cmdb sourcetype.

I want to fix the value of the BIA field for this hosts.

I try to use a lot of tings like...
| eval BIA = if( len(BIA)==0, "FIX", BIA)
but is not running fine.

Can someone help me?

Re: empty fields after a left join

bowesmana — Mon, 13 Mar 2023 22:24:51 GMT

You can't use the eval test as BIA is a null field in those events, so use fillnull instead

| fillnull BIA value="FIX"

Re: empty fields after a left join

bowesmana — Mon, 13 Mar 2023 22:27:59 GMT

Using join is not generally a good idea in Splunk as it has limitations which may silently affect your data.

join functionality is generally achieved in Splunk using this construct

index=lab (sourcetype=A OR sourcetype=cmdb) | stats values(*) as * by src_host

This will generally always be faster than a join and not have limitations.

The 'values(*) as *' can be tailored to carry forward whatever fields you want in the resultant rows.

Re: empty fields after a left join

pierre_weg — Tue, 14 Mar 2023 10:56:13 GMT

Great! Great! Great! @bowesmana

Thanks for your help!

Re: Why are there empty fields after a left join?

pierre_weg — Thu, 23 Mar 2023 14:50:20 GMT

Hi @bowesmana
I found an issue...
In sourcetype=cmdb the all events have the field BIA with "H", "M" ou "L" values.
After the join, and using "fillnull", if I use "fillnull BIA value="E"", 100% of the events have one of the 4 values, but if I use "fillnull BIA value="H"" 100% of the events will have "H" value.

what I'ḿ doing wrong?

Re: Why are there empty fields after a left join?

bowesmana — Fri, 24 Mar 2023 02:10:30 GMT

Can you post your full search

Re: Why are there empty fields after a left join?

pierre_weg — Fri, 24 Mar 2023 11:07:15 GMT

I found the mistake...

It was a syntax error

The right way is

| fillnull value="FIX" BIA

not

| fillnull BIA value="FIX"

Thanks again!