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
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
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
@somesoni2 - is there any advantage to this approach using eval
and coalesce
over using fillnull
?
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
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!
Works - thx so much!
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.
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