Splunk Search

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

jwalzerpitt
Influencer

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

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

elliotproebstel
Champion

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

0 Karma

somesoni2
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

elliotproebstel
Champion

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

jwalzerpitt
Influencer

Works - thx so much!

0 Karma

somesoni2
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

jwalzerpitt
Influencer

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

host_name src_ip 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':

host_name src_ip 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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...