Help Regarding search query

[devdattajogleka]

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.  

[marysan]

Hi . this query should be worked for you :
| streamstats count,sum(file_count) as total_sum | eval avg=total_sum/count

[ITWhisperer]

| streamstats count sum(file_count) as total
| eval average=round(total/count,1)