topic Re: Average Weekly Value in Splunk Search

Average Weekly Value

henryt1 — Mon, 28 Sep 2020 11:35:37 GMT

So I was unable to find an answer on here that helped with what I'm trying to do. When I run the following query I get a single number of logins per week.

host="web01.inno-360.com" AND source="/var/log/apache2/pg_ssl_access.log" AND \/projects\/*\/talent-ecosystems\/createlist | stats count by source

However I am also wanting to find out what the average number of logins is (per week) since the beginning of the year. Would anyone know how to do this? Thanks in advance.

Re: Average Weekly Value

sowings — Thu, 29 Mar 2012 14:24:51 GMT

I'd use an eval to set a new variable to the week number (see strftime and %V), then use that value as the "by" field in a stats command.

Re: Average Weekly Value

henryt1 — Thu, 29 Mar 2012 14:27:44 GMT

Thank you for the input, I've never used eval before (I'm new to Splunk) so what would that query look like?

Re: Average Weekly Value

sowings — Thu, 29 Mar 2012 14:35:03 GMT

Something like "search=foo ... | stats command_to_get_daily_count | eval wnum=strftime(_time, "%V") | stats avg(count) by wnum"

Re: Average Weekly Value

Sarmbrister — Tue, 29 Sep 2020 11:32:22 GMT

I know this is an old post, but I created the below search query below. Should work for anyone looking for their average over the past 7 days. It's a big ugly search and I'm sure there is a way to simply this or to heavily utilize eval but this is what I ended up with. Hope it helps everyone.
index=_internal source=*license_usage.log type=usage

| eval GB = b/1024/1024/1024

| timechart span=1d sum(GB) by pool

| eval Time=strftime(_time, "%m/%d/%Y")

| fieldformat auto_generated_pool_enterprise=tostring(auto_generated_pool_enterprise, "commas"). " GB"

| fields - _time

| addcoltotals auto_generated_pool_enterprise label=Total labelfield=weeklytotal
| fields Time,auto_generated_pool_enterprise, weeklytotal
| search weeklytotal=Total auto_generated_pool_enterprise>1*GB
| eval weekly_average=(auto_generated_pool_enterprise/7)
|eval Total_weekly_average=round(weekly_average,2)
| table Total_weekly_average
| rename Total_weekly_average AS "Average over the past 7 days"