Splunk Search

How to add missing values to stats command result?

joydeep741
Path Finder

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.

Tags (3)
0 Karma
1 Solution

niketn
Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

joydeep741
Path Finder

Awesome man.

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

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Shan
Builder

@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
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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

Happy Splunking!
0 Karma

joydeep741
Path Finder

timechart span=1d count by CREATED_DATE

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...