Splunk Search

Why are there empty fields after a left join?

pierre_weg
Path Finder

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?

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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

​| fillnull BIA value="FIX"

View solution in original post

pierre_weg
Path Finder

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?

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Can you post your full search

0 Karma

pierre_weg
Path Finder

I found the mistake...

It was a syntax error

The right way is

| fillnull value="FIX" BIA

not

| fillnull BIA value="FIX"

 

Thanks again!

0 Karma

pierre_weg
Path Finder

Great! Great! Great! @bowesmana 

Thanks for your help!

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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.

 

bowesmana
SplunkTrust
SplunkTrust

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

​| fillnull BIA value="FIX"
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...