Splunk Search

Fill in 0 for timechart with missing values

the_wolverine
Champion

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

1 Solution

the_wolverine
Champion

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

View solution in original post

puma_splunk
Engager

Try this simple Method

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

gf13579
Communicator

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)

tdepuy
Path Finder

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

0 Karma

sureshkandi
Explorer

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

0 Karma

proylea
Contributor

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

0 Karma

the_wolverine
Champion

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

yvassilyeva
Path Finder

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!

0 Karma
Get Updates on the Splunk Community!

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...

Want to Reduce Costs, Mitigate Risk, Improve Performance, or Increase Efficiencies? ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...