topic Re: How to add missing values to stats command result? in Splunk Search

How to add missing values to stats command result?

joydeep741 — Thu, 09 Aug 2018 05:13:15 GMT

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.

Re: How to add missing values to stats command result?

renjith_nair — Thu, 09 Aug 2018 06:00:58 GMT

@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

Re: How to add missing values to stats command result?

Shan — Thu, 09 Aug 2018 06:49:25 GMT

@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

Re: How to add missing values to stats command result?

joydeep741 — Thu, 09 Aug 2018 06:51:38 GMT

timechart span=1d count by CREATED_DATE

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

Re: How to add missing values to stats command result?

niketn — Thu, 09 Aug 2018 07:03:57 GMT

@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

Re: How to add missing values to stats command result?

renjith_nair — Tue, 29 Sep 2020 20:53:16 GMT

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

Re: How to add missing values to stats command result?

joydeep741 — Thu, 09 Aug 2018 07:46:59 GMT

Awesome man.

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

Re: How to add missing values to stats command result?

niketn — Tue, 29 Sep 2020 20:49:41 GMT

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