How to fill the null values in search results

[jgcsco]

How can I fill null value in the following result with desired value, e.g. 0:

mysearch | stats count by host

I would like to have the following result format

host1 xx
host2 0 (which has the null result from the search)
host3 yy
host4 zz
host5 0 (which has the null result from the search)

Any suggestions? Please help.

Thanks

[woodcock · Accepted solution]

Based on your clarification, you need this:

index=* OR index=_* | stats count(eval(like(lower(_raw), "%error%"))) AS Errors by host

[jgcsco]

Thanks woodcock. The only issue is that the log on each host is fairly large in terms of size, and the search here seem a bit inefficient, but it does provide the result I am looking for.

[woodcock]

The most time spent is in the lower command so this should be faster:

 index=* OR index=_* | stats count(eval(match(_raw, "[eE][rR][rR][oO][rR]"))) AS Errors by host

[jgcsco]

Thanks for your quick response, I would like to include the null results and fill then with 0. Following is the search string:

host=* "Error" |stats count by host

Basically, I am search the log file on all the hosts to find out error messages, for the host that does not have any error, I would like to set the value with "0".

Thanks,

[woodcock]

Generally you just add this:

... | fillnull value=0

But it is hard to say because your question does not include your search and data examples.

[skoelpin]

Do you want to exclude the null results from your search or fill them with 0? If the former then try isnotnull()

Provide your search and clarification and I can help you out