I am trying to create a search that provides me with the predicted average usage of a machine during the course of a month. The data logging this information is in an SQL database, which I query (using the dbconnect app) to gather a set of Date and Duration pairs which describe when the machine was used and for how long.
Here is what my query looks like:
|dbquery ..... | rename Date as _time | timechart span=1d sum(Duration) as dailySumDuration | stats avg(dailySumDuration) as avgDuration | eval monthlyDuration=(avgDuration*30)
My issue with this query is that timechart does not fill in 0's for my empty buckets, so the daily average is only the average for days when the systems are used which overinflates my estimate.
Is there any way to force timechart to fill in empty days with 0's so I can average over them?
Replaces null values with a user specified value (default 0). Null values are those missing in a particular result, but present for some other result. If a field-list is provided, fillnull is applied to only fields in the given list (including any fields that does not exist at all). Otherwise, applies to all existing fields.