"No results found" to be represented as NULL or 0

[adityapavan18]

Hi I have a query ending

| stats count as Traffic,avg(duration) by host

It works fine if i have some logs.

If there are no logs, i get "no results found"

But is it possible to return

Traffic     avg(duration)   host
0              0            NULL

[jonuwz · Accepted solution]

Try something like this :

| stats count as Traffic, avg(duration) as duration by host 
| appendpipe [ stats count | eval Traffic=0 | eval duration=0 | eval host="NULL" | where count==0 | fields - count ]

This adds an extra line to your output with default values, but only if the result count = 0

[karthik4455]

I have a scenario where one column needs to be indicated with Zero in the instance of no result. While this seems to be working, however, it's showing other fileds as NULL.

query:
index=dailyincidents earliest=-90d@d source=FW1-HKS-01 | dedup id | stats values(customer) AS Customer count AS QuarterlyVolume by source | appendpipe [stats count | eval QuarterlyVolume=0 | where count=0 | fields - count]

I am using the above query and I'm seeing the below result:
source Customer QuarterlyVolume
1 0

I want to see the details of source and Customer as well.