How to add missing values to stats command result?

[joydeep741]

I have a search query

index=abc sourcetype=xyz | stats count by created_date

I get results like

CREATED_DATE          COUNT
2018-08-08                  12
2018-08-07                   10
2018-08-04                   05
2018-08-02                  06
2018-08-01                   03

But as you can see, some dates are not present in logs so do not appear in results.
Like 2018-08-06, 2018-08-05, 2018-08-03

I want these dates to come but with ZERO count.

The final result should look like

CREATED_DATE          COUNT
2018-08-08                  12
2018-08-07                   10
**2018-08-06                   0
2018-08-05                   0**
2018-08-04                   05
**2018-08-03                   0**
2018-08-02                  06
2018-08-01                   03

Please suggest a solution.

[niketn · Accepted solution]

@joydeep741 your Created Date as per sample Data seems to be in YYYY-mm-dd format. If you want to use it in timechart, you can try the following:

index=abc sourcetype=xyz 
| eval _time=strptime(created_date,"%Y-%m-%d")
| timechart span=1d count
| fillnnull value=0 count

PS: Final fillnull command might not be required. So test without that as well.
strptime() works based on the String Time Format in your Data. So above is based on sample dates your have provided i.e. %Y-%m-%d

[joydeep741]

Awesome man.

Often the solution to splunk problem statements are the most simplest ones and not Complex queries.

[niketn]

True but messing with _time is risky 🙂 If you have a Time Picker input based dashboard you would need to ensure that created_date filter with String Time in YYYY-mm-dd is applied based on earliest and latest time selected in the Time Picker input.

If _time and created_time are two different time not related to each other, the approach from one of my older answers can be tweaked to do this: https://answers.splunk.com/answers/578984/running-one-of-two-searches-based-on-time-picker-s.html

[Shan]

@joydeep741

Try this Query.

| makeresults 
| eval Date=mvappend("2018-08-08","2018-08-07","2018-08-04","2018-08-02","2018-08-01")
| mvexpand Date
| table Date
| appendcols  [| gentimes start=08/01/2017 end=09/30/2017 | bin span=1m endtime | stats count by endtime | eval Date=strftime(endtime, "%Y-%m-%d") | table Date     
          | table Date
          ]       
          | eventstats values(Date) as Date          
    | stats count by Date

[renjith_nair]

@joydeep741,

Try

index=abc sourcetype=xyz |timechart span=1d count by CREATED_DATE 

If your CREATED_DATE is continuous, then timechart span=1d count also should work

[joydeep741]

timechart span=1d count by CREATED_DATE

This would put created_date as column names in the result...

[renjith_nair]

As mentioned ,you could convert _time to the CREATED_DATE as CREATED_DATE=strftime(_time,"%Y-%m-%d")