Splunk Search

Timechart Dropping Empty Buckets

werz
New Member

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?

Thanks

Tags (3)
0 Karma

mishin
Explorer

I think

| timechart cont=FALSE sum(count) by IP

help you

0 Karma

sdaniels
Splunk Employee
Splunk Employee

You'll want to use fillnull.

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.

http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Fillnull

0 Karma

linu1988
Champion
0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!