trying to return 0 with stats and group by in Search

[pratik420]

I am trying to run a search query where expected value is '0' when a process is not running. It won't populate '0' when using stats and grouping the search by _time. Tried using fillnull but did not help. Can someone advise ? ?

index=OSMetrics sourcetype=ps process="MyAgent.jar*"|bin _time span=1h| stats count(process) as running by _time |fillnull running value=0

[somesoni2]

IF the base search doesn't have any record, an aggregation by a field name doesn't return any result (since there is not field value to begin with)., The fillnull command also works to replace null values only if there are events available. For your requirement, try this workaround

index=OSMetrics sourcetype=ps process="MyAgent.jar*"|bin _time span=1h| stats count(process) as running by _time
| appendpipe [| stats count as running| where running=0 ]

The appendpipe subsearch basically adds an row with running=0 if there are no results returned from the search before it. it will not do anything if there are any rows. You should get a row with null _time field and running field with 0 as value, when there are no result from base search.

[niketn]

@pratik420 Use timechart instead

index=OSMetrics sourcetype=ps process="MyAgent.jar*"
| timechart span="1h" count(process) as running 

[pratik420]

thanks for the quick response, timechart won't work since i need to group by multiple fields. Without the grouping, timechart works like a charm

index=OSMetrics sourcetype=ps process="MyAgent.jar*"|bin _time span=1h| stats count(process) as running by _time host process

[niketn]

@pratik420, In your question, time was the only aggregate field so I thought there would have been only one series. But for multiple aggregates with timechart you can try the following:

 index=OSMetrics sourcetype=ps process="MyAgent.jar*"
 | eval key=host." - ".process
 | timechart span="1h" count by key