Splunk Search

Help Regarding search query

devdattajogleka
Explorer

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.  

Labels (2)
0 Karma

marysan
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| streamstats count sum(file_count) as total
| eval average=round(total/count,1)
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...