Hello,
I am still somewhat new to Splunk and have the following issue.
I have a case where I want to count up the 'things' that occur each day and display this count in a chart. However, there are days when nothing happens. Therefore, gaps occur along the timeline. This is a relatively easy problem to solve using 'chart count by timestamp span=1d'. This covers the entire range of the indexed events and displays a count of '0' for the interpolated days. So far, so good.
What I have not yet been able to figure out is how to extend the timeline from the last indexed event to the present day. For example, let's say that the timestamp of the last indexed event is 10/15/2018. But that no other events have occurred since then. If today is 10/18/2018, then I am missing 3 days that are not represented along the x-axis. Yet, I want those 3 missing days to appear in the chart with a count of '0'.
Note also that I am not interested in the time that something was indexed. The time series data that is to be displayed comes directly from the data itself and there may or may not be any relationship to the index time.
In order to better illustrate, here is a search using 'makeresults' that generates a table with a random count of 'things' every third day.
| makeresults count=15
| streamstats count
| where 0 = count % 3
| eval tMod="-".count."d@d"
| eval timestamp=relative_time(now(), tMod)
| eval things=random() % 9
| eval things=if(things=0,10,things)
| table timestamp, things
| sort -timestamp
| fieldformat timestamp=strftime(timestamp, "%a, %Y-%m-%d")
The results are as follows.
Date | Things
Mon, 2018-10-15 | 10
Fri, 2018-10-12 | 6
Tue, 2018-10-09 | 7
Sat, 2018-10-06 | 1
Wed, 2018-10-03 | 4
Here is an extended search that demonstrates interpolation along the timeline. It uses 'makecontinuous' and 'fillnull' to produce the desired results. NOTE: In practice I would use 'chart' not 'table', thereby eliminating the need for 'makecontinuous' and 'fillnull'.
| makeresults count=15
| streamstats count
| where 0 = count % 3
| eval tMod="-".count."d@d"
| eval timestamp=relative_time(now(), tMod)
| eval things=random() % 9
| eval things=if(things=0,10,things)
| table timestamp, things
| makecontinuous timestamp span=1d
| fillnull value=0
| sort -timestamp
| fieldformat timestamp=strftime(timestamp, "%a, %Y-%m-%d")
The results are as follows.
Date | Things
Mon, 2018-10-15 | 10
Sun, 2018-10-14 | 0
Sat, 2018-10-13 | 0
Fri, 2018-10-12 | 6
Thu, 2018-10-11 | 0
Wed, 2018-10-10 | 0
Tue, 2018-10-09 | 7
Mon, 2018-10-08 | 0
Sun, 2018-10-07 | 0
Sat, 2018-10-06 | 1
Fri, 2018-10-05 | 0
Thu, 2018-10-04 | 0
Wed, 2018-10-03 | 4
So to reiterate my question, how do I fill in the timeline to show each day after 10/15/2018 until today, for example 10/18/2018, such that the results look like the table below?
Date | Things
Thu, 2018-10-18 | 0
Wed, 2018-10-17 | 0
Tue, 2018-10-16 | 0
Mon, 2018-10-15 | 10
Sun, 2018-10-14 | 0
Sat, 2018-10-13 | 0
Fri, 2018-10-12 | 6
Thu, 2018-10-11 | 0
Wed, 2018-10-10 | 0
Tue, 2018-10-09 | 7
Mon, 2018-10-08 | 0
Sun, 2018-10-07 | 0
Sat, 2018-10-06 | 1
Fri, 2018-10-05 | 0
Thu, 2018-10-04 | 0
Wed, 2018-10-03 | 4
Thank you
... View more