topic Re: how to display a 0 result instead an empty result in Splunk Search

how to display a 0 result instead an empty result

jip31 — Thu, 21 Feb 2019 10:48:52 GMT

I use the search below and I would like to have a 0 results displayed when there is no events corresponding
could you help me please?? Thanks

    eventtype="x" Name="x" 
    | fields Name, host 
    | dedup host
    | stats count by host

Re: how to display a 0 result instead an empty result

chrisyounger — Thu, 21 Feb 2019 10:54:14 GMT

This is one way to do it. First create a CSV of all the valid hosts you want to show with a zero value. Call this hosts.csv and make sure it has a column called "host". Then change the query to be like so:

 eventtype="x" Name="x" 
| fields Name, host 
| dedup host
| stats count by host
| append [|inputlookup hosts.csv]
| stats sum(count) as count by host

Re: how to display a 0 result instead an empty result

vik_splunk — Tue, 29 Sep 2020 23:19:22 GMT

A more elegant way would be to use a combination of stats and eval. Please try this run anywhere example which I am sure can be customized for your use case. Also, instead of doing dedup and then count, dc(distinct count) can be used.

Try replacing log_level with DEBUG or any non standard type in the search to see it returns 0.

index=_internal sourcetype=splunkd log_level="ERROR"
|fields component,host
|stats dc(eval(if(isnull(host),0,host))) AS Count

Hope this helps!

Re: how to display a 0 result instead an empty result

vnravikumar — Thu, 21 Feb 2019 11:37:38 GMT

Hi @jip31

Try this and let me know

eventtype="x" Name="x" 
     | fields Name, host 
     | dedup host
     | stats count by host
     | appendpipe [stats count | where count=0 | eval host="Specify your text here"]

Re: how to display a 0 result instead an empty result

vnravikumar — Thu, 21 Feb 2019 12:43:55 GMT

@jip31, have you tried?

Re: how to display a 0 result instead an empty result

jip31 — Thu, 21 Feb 2019 12:44:34 GMT

thank but its impossible to use a lookup...

Re: how to display a 0 result instead an empty result

jip31 — Thu, 21 Feb 2019 12:53:28 GMT

thanks
if i put only appendpipe [stats count | where count=0] its enough?
what is the use of eval host="Specify your text here"]??

Re: how to display a 0 result instead an empty result

vnravikumar — Thu, 21 Feb 2019 12:56:55 GMT

yes its enough, but under host column it will display empty. If you want to add some text info, you can specify

Re: how to display a 0 result instead an empty result

vik_splunk — Tue, 29 Sep 2020 23:19:24 GMT

@jip31

An elegant way to do this without lookups would be to use eval and stats as can be seen in this run anywhere example which I am sure can be customized for your use case.

Also, you won't require dedup followed by stats as dc(distinct count) does the same and fields can be used to return only the field you count upon as the filter has been done earlier.

index=_internal sourcetype=splunkd log_level="ERROR"
|fields host
|stats dc(eval(if(isnull(host),0,host))) AS Count

Re: how to display a 0 result instead an empty result

jip31 — Thu, 21 Feb 2019 13:06:56 GMT

ok its not a problem because I done a fields - host

Re: how to display a 0 result instead an empty result

vnravikumar — Thu, 21 Feb 2019 13:09:41 GMT

if it works, please accept my answer.

Re: how to display a 0 result instead an empty result

vik_splunk — Thu, 21 Feb 2019 17:18:14 GMT

@jip31

Which solution worked for you?

The stats or the lookup based solution?

Re: how to display a 0 result instead an empty result

jip31 — Fri, 22 Feb 2019 14:04:56 GMT

sorry but I have an issue it works but even if there is results I have...........0 instead results...

Re: how to display a 0 result instead an empty result

jip31 — Fri, 22 Feb 2019 17:22:50 GMT

Thanks its interesting

Re: how to display a 0 result instead an empty result

jip31 — Fri, 22 Feb 2019 17:24:54 GMT

Thé stats but if there is event i have also 0 instead résult...