Splunk Search

How to create a linechart showing the previous 6 months with each point being the sum of the previous 6 months?

glenngermiathen
Path Finder

Im trying to show a trend using a linechart. It should show the previous 6 months and have a data point once for each month. The data point should be sum of events for the previous 6 months, and the labels across the x-axis should just be the month name. For example if today is Dec 15, the x-axis would have the labels July, Aug, Sept, Oct, Nov, Dec. The July data point would be the sum of the value of field1 for all events that occurred in Feb-July. The Dec data point would be the sum of the value of field1 for July - the current date in Dec. Finally, if for some reason there are no events in that time period the line should not go to 0, but the last two data points should connect. How would I create a search like this?

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

This should be what you're looking for, run over @mon-10mon to now:

index=foo sourcetype=bar etc... | timechart span=1mon sum(field1) as monthly_sum
| streamstats window=6 sum(monthly_sum) as sixmonthly_sum | fields - monthly_sum
| where _time >= relative_time(now(), "@mon-5mon")
| eval sixmonthly_sum = case(sixmonthly_sum>0, sixmonthly_sum)
  • search for your data, compute monthly sums
  • compute running six-month-sums, drop monthly sums
  • drop first five months
  • remove zeroes, make sure the line chart is set to connect on missing values

martin_mueller
SplunkTrust
SplunkTrust

So... there should be no summing up of previous months, just the latest value over that six-month window?

0 Karma

glenngermiathen
Path Finder

In the case of one asset their is no sum, but with multiple assets you would sum the latest value of each asset over the six month windows. For example if you had two assets:
Asset A, Oct 1: 100
Asset A, Oct 15: 500
Asset A, Dec 1: 250
Asset B, Oct 2: 200
Asset B, Nov 1: 300

Oct = 700 (sum Asset A oct 15 and Asset B Oct 2)
Nov = 800 (sum Asset A Oct 15 and Asset B Nov 1)
Dec = 550 (sun Asset A Dec 1 and Asset B Nov1)

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

My confusion isn't decreasing 😄 let's whip up an example for just one host:

June 1st: 1
June 15th: 2
July 1st: 4
July 15th: 8
July 31st: 16
August 1st: 32
August 15th: 64
August 31st: 128
September 1st: 256
October 1st: 512
October 15th: 1024
November 1st: 2048
December 1st: 4096

What should the value be for November? What value for December?

0 Karma

glenngermiathen
Path Finder

Sorry for the late reply, the value for Nov should be 2048 and Dec 4096

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

So... you don't want to compute a sum of field1 over a six-month period but rather use the field value from the latest event? I'm confused.

0 Karma

glenngermiathen
Path Finder

Sorry I didn't explain very clearly. field1is the "Event Count", and every host has an event count associated with it. A host can be listed many times in the 6 month periods, so I want to count each host once in that period using the most recent occurrence of that host. Then with the list of unique hosts sum the "event count" field for that 6 month period. For example lets say hostA shows up 2 times each month. For the July-Dec bucket only the most recent event count for hostA would be summed up with the event counts for the most recent occurrences of the other hosts. Then For the June-Nov bucket it would be the most recent occurrence in Nov that was used the in summation. Hope this is clearer.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

To get the latest value per host per month, use this beginning:

index=foo sourcetype=bar etc... | bin span=1mon _time as month | dedup host month | timechart ...
0 Karma

glenngermiathen
Path Finder

Right, but this would dedup the 1 month periods, but Im need to do it for the 6 month period. Am I correct that if I did
| bin span=6mon that it would group distinct periods ie Jan-Jun July-Dec, rather than Jun-Nov July-Dec?

0 Karma

glenngermiathen
Path Finder

Thanks that works great! I have one more kink I need to work out though. For each of the 6 month periods the host event count fields can repeat, and I only need to sum the most recent event count for all the hosts in that period. I have data sorted, and then I want to dedup on the hostname for each 6 month period. How would I go about doing that? My data looks like:
Host "Event Count" _time
HostA 500 time
HostA 450 time
HostB 1000 time...

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