timechart an average count

[crazyeva]

Hi, I want to get a chart as 'timechart avgcount span=1d' or 'stats avgcount by _time, span=1d'
in which, avgcount means average of last 5 days.
That means each point or bar in this chart, is the average count of last 5 days,(count_of_5d/5).instad of total of 1 day.
And I want to apply this search to same historical data. so i can not use Summary search for fresh incomeing data.
I have some ideas like:
events|append [events|eval _time=_time+1day]|append [events|eval _time=_time+2day]|append ......
|timechart count/5 span=1d
But this is too thick a search for my 1.6 billion events
Is there any better ways? Thanks!

[jtrucks]

Ayn is right. Use a summary index.

Do something like this:

Every day just after midnight run:

earliest=-1d@d latest=-0d@d your search | timechart span=1d count as dailycount | collect index=yoursummaryindex marker="datalabel=onedaycount"

Then run this a bit later after that count is done:

earliest=-5d@d latest=-0d@d index=yoursummaryindex datalabel=onedaycount | stats avg(dailycount) | collect index=yoursummaryindex marker="datalabel=fivedayavg"

Then your search can be something like this instead:

index=yoursummaryindex datalabel=fivedayavg | timechart span=1d

... to make your chart.

[crazyeva]

Thank you very much!
I think what exactly i am looking for is in the document which Ayn recommended, "backfill script"
But I am not familiar with python. so it will take me a while to understand.
I have a temporary solution:
overlying events COUNT of day,5 times.each time:_time+86400:
1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8
1 2 3 4 5 6 7
1 2 3 4 5 6
1 2 3 4 5
although not professional, but it works.

[Ayn · Accepted solution]

You should setup summary indexing. Run a query once a day that gathers the count for that day and puts the results in a summary index. Then, query the summary index. Quick and easy.

[crazyeva]

Thank you!

[Ayn]

http://docs.splunk.com/Documentation/Splunk/5.0.4/Knowledge/Managesummaryindexgapsandoverlaps

[crazyeva]

Thank you for your help!
But I think Summary indexing is batter at real-time data, not old data. And my data was crossing 6 months. Even I setup running a query once a day with Summary. I will have to spend 6 months to get the whole results...
Is there a simple search, or maybe several of them step by step, to get the results at once?

[rturk]

So to clarify, for any given day, you want the chart to reflect what the 5 day average is on that day?