Splunk Search

How do I return fields that do not have a corresponding value in another field?

Motivator

I am running the following search:

index="malwarebytes" sourcetype=malwarebytes NOT threat_name=pu* 
| lookup ip_cidr cidr_range as src_ip 
| stats count by host_name, src_ip, user, threat_name, firewall, context, zone
| sort -count

If I add the field 'dst' to pull the IP value, I am losing certain threat_names from the report as there is no corresponding value for the 'dst' field. How can I return a null value for those threat names that do not contain a value for the 'dst' field?

Thx

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Try this

index="malwarebytes" sourcetype=malwarebytes NOT threat_name=pu* 
 | lookup ip_cidr cidr_range as src_ip 
 | eval dst=coalesce(dst,"null")
 | stats count by host_name, src_ip, user, threat_name, dst, firewall, context, zone
 | sort -count

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

Try this

index="malwarebytes" sourcetype=malwarebytes NOT threat_name=pu* 
 | lookup ip_cidr cidr_range as src_ip 
 | eval dst=coalesce(dst,"null")
 | stats count by host_name, src_ip, user, threat_name, dst, firewall, context, zone
 | sort -count

View solution in original post

0 Karma

@somesoni2 - is there any advantage to this approach using eval and coalesce over using fillnull?

0 Karma

SplunkTrust
SplunkTrust

If you consider syntax | eval field1=coalesce(field1,"NullStringValue") and | fillnull value="NullStringValue" field1, they both perform same, so you can use them interchangeably. More specific case of eval-coalesce would be, for a single field, take first non-null values, comparing multiple fields. E.g. | eval host=coalesce(src_host,dest_host,"NullStringValue"). So with eval-coalesce, you can update a single field but can reference multiple fields.
With fillnull, you can update multiple fields but with single replacement of NULL (splunk's field value null) with a string. E.g. | fillnull value="NullStringValue" field1 field2 field3

0 Karma

Yes, absolutely. I just wondered if you chose the eval/coalesce option here over the more intuitive (to me) fillnull option because of some performance or other subtle consideration. Thanks for the reply!

0 Karma

Motivator

Works - thx so much!

0 Karma

SplunkTrust
SplunkTrust

So you want to return 0 count for those threat_name OR want a null (or any suitable literal string) for field dst? A sample current and expected output would be helpful here.

0 Karma

Motivator

Here is how it currently looks w/o the 'dst' field:

hostname srcip user threat_name firewall context zone count
abc x.x.x.x User A Rogue.SearchEncrypt FW 1 a-b-c Zone 1 27
def x.x.x.x User B Type: outgoing, Port: 56327, Process: iexplore.exe FW 6 l-m-n Zone 6 1

I'd like a null to be returned for 'dst':

hostname srcip user threat_name dst firewall context zone count
abc x.x.x.x User A Rogue.SearchEncrypt null FW 1 a-b-c Zone 1 27
def x.x.x.x User B Type: outgoing, Port: 56327, Process: iexplore.exe 8.8.8.8 FW 6 l-m-n Zone 6 1

Thx

0 Karma