topic Re: How to make a line chart that shows 6 months of data with each datapoint being a sum of the previous 6 months? in Splunk Search

How to make a line chart that shows 6 months of data with each datapoint being a sum of the previous 6 months?

glenngermiathen — Tue, 29 Sep 2020 17:12:05 GMT

Im trying to show a trend in event data by platform. I want to create a line chart showing the last 6 months with one data point for each month. That data point should be the sum of an event field for the previous 6 months. Assets in the platform group can be repeated in that 6 month period, so I only want the most recent value for that period. My data looks like
Platform Asset Event_count date
Windows AssetA 25 1/1/2017
Windows AssetA 32 1/9/2017
Windows AssetA 13 8/1/2017
Linux AssetA 56
Linux AssetB 24

This is what I have come up with, but it is only showing one data point, and I would like the x-axis values to just be the month name.
index=foo
| fields asset platform event_count
| bucket _time span=6mon
| dedup asset date
| timechart span=1mon sum(event_count) by platform

Im sure this isn't close to correct, so any help is greatly appreciated.

Re: How to make a line chart that shows 6 months of data with each datapoint being a sum of the previous 6 months?

Richfez — Tue, 12 Dec 2017 12:50:27 GMT

Streamstats may be of use.

index=foo earliest=-1y
| fields asset platform event_count
| bucket _time span=1mon
| dedup asset date
| streamstats window=6 sum(event_count) AS 6MonthTotal BY platform
| table _time, platform, 6MonthTotal

That's probably missing some of your final data munging and fiddling, but I think that's the core of what you'll need. Once you get to this point I think you'll find is reasonably easy to do what you need to do, though feel free to post back if you need more help!

Note I need coffee yet this morning so you might need a |reverse ahead of the streamstats.

Happy Splunking,
Rich

Re: How to make a line chart that shows 6 months of data with each datapoint being a sum of the previous 6 months?

cmerriman — Tue, 12 Dec 2017 12:59:51 GMT

something like this might help you. just start with the timechart and tweak it as needed.

|makeresults|eval data="platform=Windows,asset=AssetA,event_count=25,date=1483296553 platform=Windows,asset=AssetA,event_count=32,date=1483987753  platform=Windows,asset=AssetA,event_count=13,date=1501609753 platform=Linux,asset=AssetA,event_count=56,date=1493660953 platform=Linux,asset=AssetB,event_count=24,date=1496684953"|makemv data|mvexpand data|rename data as _raw|kv|eval _time=date
| timechart span=1mon sum(event_count) as event_count by platform|makecontinuous span=1mon|fillnull value=0|reverse|streamstats window=6 sum(*) as 6_mo_total_*|reverse|fields _time 6_mo*

the problem with the query you had started with was bucketing 6 months and then trying to bucket 1 month in the timechart. because you had already bucketed 6 months, splunk couldn't get more granular than that.