topic Re: Fill in 0 for timechart with missing values in Splunk Search

Fill in 0 for timechart with missing values

the_wolverine — Tue, 14 Jan 2014 05:28:26 GMT

I'm generating a chart with event count by date. The problem is for dates with no events, the chart is empty. I want it to display 0 for those dates and setting "treat null as zero" OR connect does not work. I wind up with only counts for the dates that have counts.

How to workaround?

Query:

index=main startdaysago=10 | timechart span=1d count

Re: Fill in 0 for timechart with missing values

the_wolverine — Tue, 14 Jan 2014 05:29:56 GMT

Create dummy data and append it. This will fill in the count of 0 of days missing events to count:

index=main startdaysago=10 | append [| search index=_internal earliest=-30d | head 1000 | fields - * | fields - _* | streamstats count | bucket _time span=1d | eval count=0] | timechart span=1d count

Re: Fill in 0 for timechart with missing values

proylea — Fri, 21 Oct 2016 03:43:55 GMT

If you think outside the box a little you could use the panel display feature in the dashboard to just show a different/dummy display when there are no results returned.

For instance I have some single value metrics on a dashboard that normally show N/A if there are no results returned and it also makes the overall panel look a bit untidy.

So I did this:

     <single depends="$result1$">
        <title>'Share' Tracking</title>
        <search>
          <query>| inputlookup user_usage.csv  | search "click on \\\"SHARE\\\"" (name="Toll_DPM_BT_PADATA_DETAILEDUSERACTIONS_AllEnv") (application="*")  NOT (GomezAgent) UserName!="*tollgroup.com" | timechart count span=7d</query>
          <earliest>-30d@d</earliest>
          <latest>now</latest>
          <progress>
            <condition match="'job.resultCount' < 1">
              <set token="fill1">true</set>
              <unset token="result1"></unset>
            </condition>
            <condition>
              <set token="result1"></set>
              <unset token="fill1">true</unset>
            </condition>
          </progress>
        </search>
        <option name="drilldown">all</option>
        <option name="colorBy">value</option>
        <option name="colorMode">block</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x65a637","0x65a637"]</option>
        <option name="rangeValues">[1]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">1</option>
        <option name="link.visible">false</option>
        <option name="underLabel">Compared to Previous Week</option>
        <option name="refresh.time.visible">false</option>
        <drilldown>
          <set token="detail-track">true</set>
        </drilldown>
      </single>
      <single depends="$fill1$">
        <title>'Share' Tracking</title>
        <search>
          <query>| inputlookup fillnull.csv  | timechart count span=7d</query>
          <earliest>-30d@d</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">none</option>
        <option name="colorBy">trend</option>
        <option name="colorMode">block</option>
        <option name="numberPrecision">0</option>
        <option name="rangeColors">["0x65a637","0x65a637"]</option>
        <option name="rangeValues">[1]</option>
        <option name="showSparkline">1</option>
        <option name="showTrendIndicator">1</option>
        <option name="trendColorInterpretation">standard</option>
        <option name="trendDisplayMode">absolute</option>
        <option name="unitPosition">after</option>
        <option name="useColors">1</option>
        <option name="useThousandSeparators">1</option>
        <option name="link.visible">false</option>
        <option name="underLabel">Compared to Previous Week</option>
        <option name="refresh.time.visible">false</option>
      </single>

The top box shows if there is a result returned and the bottom one shows a dummy result containing zeros if there is no results returned.

The lookup table fillnull.csv would just be something like this

_time count
date 0

Re: Fill in 0 for timechart with missing values

gf13579 — Thu, 10 Aug 2017 08:30:03 GMT

Couldn't you rename your timecharted count value, then use an eval and if to replace null values with 0?

| timechart span=1d count as val | eval val = if(isnull(val),0,val)

Re: Fill in 0 for timechart with missing values

puma_splunk — Tue, 27 Mar 2018 23:18:43 GMT

Try this simple Method

index=main startdaysago=10 | timechart span=1d count |fillnull

Re: Fill in 0 for timechart with missing values

tdepuy — Wed, 18 Apr 2018 23:44:38 GMT

This was way simpler for me. I'm not sure why you'd want to do a subsearch as in the accepted answer. Thanks!

Re: Fill in 0 for timechart with missing values

sureshkandi — Mon, 25 Feb 2019 09:34:50 GMT

this will work only when result count is greater than 0 and has null values in it but if result is 0 then this is not working

Re: Fill in 0 for timechart with missing values

yvassilyeva — Wed, 07 Jul 2021 10:54:21 GMT

Hi, thank you for the response. It works, but what if I have multiple fields in my stats but only want to show 0s for one of them? Thank you very much!