Splunk Search

How does one continue a timeline along the x-axis to include each time increment after some last event until today?

tanglino
Engager

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

0 Karma
1 Solution

tanglino
Engager

Okay, I may have answered my own question. I was able to achieve the desired results by creating a dummy event.

| makeresults count=15
| streamstats count
| where 0 = count % 3
| eval tMod="-".count."d@d"
| eval timestamp=relative_time(now(), tMod)
| eval things=random() % 10
| eval things=if(things=0,10,things)
| append [search | stats count | eval timestamp=relative_time(now(),"@d") | eval things=0]
| table timestamp, things
| sort -timestamp
| fieldformat timestamp=strftime(timestamp, "%a, %Y-%m-%d")

Now the results appear as follows.

Date | Things
Mon, 2018-10-18 | 0
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

Taking it to the next step interpolates the missing days and makes the timeline continuous up to the present day.

| makeresults count=15
| streamstats count
| where 0 = count % 3
| eval tMod="-".count."d@d"
| eval timestamp=relative_time(now(), tMod)
| eval things=random() % 10
| eval things=if(things=0,10,things)
| append [search | stats count | eval timestamp=relative_time(now(),"@d") | eval things=0]
| 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.

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

View solution in original post

0 Karma

tanglino
Engager

Okay, I may have answered my own question. I was able to achieve the desired results by creating a dummy event.

| makeresults count=15
| streamstats count
| where 0 = count % 3
| eval tMod="-".count."d@d"
| eval timestamp=relative_time(now(), tMod)
| eval things=random() % 10
| eval things=if(things=0,10,things)
| append [search | stats count | eval timestamp=relative_time(now(),"@d") | eval things=0]
| table timestamp, things
| sort -timestamp
| fieldformat timestamp=strftime(timestamp, "%a, %Y-%m-%d")

Now the results appear as follows.

Date | Things
Mon, 2018-10-18 | 0
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

Taking it to the next step interpolates the missing days and makes the timeline continuous up to the present day.

| makeresults count=15
| streamstats count
| where 0 = count % 3
| eval tMod="-".count."d@d"
| eval timestamp=relative_time(now(), tMod)
| eval things=random() % 10
| eval things=if(things=0,10,things)
| append [search | stats count | eval timestamp=relative_time(now(),"@d") | eval things=0]
| 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.

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

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 ...