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.
| fields asset platform eventcount
| bucket _time span=6mon
| dedup asset date
| timechart span=1mon sum(eventcount) by platform
Im sure this isn't close to correct, so any help is greatly appreciated.
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.
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.