Hi,
I have following splunk query.
| dbxquery connection="FFconed_feTenant" query="select count(file_name) as file_count, DATE_FORMAT(created_at,\"%m/%d/%y %W\") as date from ida_files_inventory
where created_at > Date_sub(Curdate(), INTERVAL 7 Day) and created_at < Curdate() group by DATE_FORMAT(created_at,\"%m/%d/%y %W\")"
It gives me the per-day count of files received in last 7 days along with the date. The result is as follows.
date file_count
06/07/22 Tuesday 79
06/08/22 Wednesday 46
06/09/22 Thursday 57
06/10/22 Friday 5
06/11/22 Saturday 5
06/12/22 Sunday 227
06/13/22 Monday 187
I want to calculate the running averages of file_counts for all these days.
For e.g.
for 1st day, running average is 79/1 = 79
for 2nd day, running average is 79+46/2 = 62.5
for 3rd day, running average is 79+46+57/3 = 60.67
and so on.
For this I want to write a query. Please help me with this.
Hi . this query should be worked for you :
| streamstats count,sum(file_count) as total_sum | eval avg=total_sum/count
| streamstats count sum(file_count) as total
| eval average=round(total/count,1)