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?
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)
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...
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 ...
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?
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.
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.
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?
Sorry for the late reply, the value for Nov should be 2048 and Dec 4096
So... there should be no summing up of previous months, just the latest value over that six-month window?