topic Re: How to create a linechart showing the previous 6 months with each point being the sum of the previous 6 months? in Splunk Search

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

glenngermiathen — Sat, 09 Dec 2017 04:30:13 GMT

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?

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

martin_mueller — Sat, 09 Dec 2017 23:07:14 GMT

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

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

glenngermiathen — Sun, 10 Dec 2017 21:44:10 GMT

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

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

martin_mueller — Sun, 10 Dec 2017 21:59:37 GMT

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

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

glenngermiathen — Sun, 10 Dec 2017 22:54:38 GMT

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?

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

martin_mueller — Sun, 10 Dec 2017 22:59:05 GMT

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.

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

glenngermiathen — Mon, 11 Dec 2017 04:38:27 GMT

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.

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

martin_mueller — Mon, 11 Dec 2017 10:55:57 GMT

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?

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

glenngermiathen — Thu, 14 Dec 2017 13:13:44 GMT

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

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

martin_mueller — Thu, 14 Dec 2017 23:06:47 GMT

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

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

glenngermiathen — Mon, 18 Dec 2017 21:03:50 GMT

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)