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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...