how to display a 0 result instead an empty result

[jip31]

hi

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

[vnravikumar · Accepted solution]

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"]

[jip31]

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

[vnravikumar]

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

[jip31]

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

[vnravikumar]

if it works, please accept my answer.

[jip31]

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

[vnravikumar]

@jip31, have you tried?

[vik_splunk]

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!

[chrisyounger]

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

[jip31]

thank but its impossible to use a lookup...

[vik_splunk]

@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

[jip31]

Thanks its interesting

[vik_splunk]

@jip31

Which solution worked for you?

The stats or the lookup based solution?

[jip31]

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