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

[jwalzerpitt]

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

[somesoni2 · Accepted solution]

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

[elliotproebstel]

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

[somesoni2]

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

[elliotproebstel]

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!

[jwalzerpitt]

Works - thx so much!

[somesoni2]

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.

[jwalzerpitt]

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