Splunk Search

Stats not returning zero counts

karthi2809
Builder

index=xxx |bucket _time span=3m |stats count by _time host IP
We are using the above stats command to get count instead of timechart just because we have two by clause fields. We need help in returning Zero count as part of stats iteslf if there is no data available.

Tags (1)
0 Karma

jplumsdaine22
Influencer

(Assuming you want zero counts for host)
You're going to have to either maintain a lookup of the hosts you are interested in, or search for all the hosts in certain time range. If you're happy with a lookup, then you can run your query like this:

| inputlookup interesting_hosts 
| fields host 
| join type=left host [ 
  search index=xxx earliest=-60m 
  | bucket _time span=3m 
  | stats count by _time host IP
  ] 
| fillnull count value=0

If you want something more dynamic, you can search for the hosts first over a longer time range than your final search

| tstats earliest=-30d count WHERE index=xxx by host 
| fields host 
| join type=left host [ 
  search index=xxx earliest=-60m
  | bucket _time span=3m 
  | stats count by _time host IP
  ] 
| fillnull count value=0
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 ...