topic Re: Average count based on day in Splunk Search

Average count based on day

th1agarajan — Wed, 16 Jan 2019 18:51:35 GMT

index="apigee" sourcetype="apigee:hec" | search DeveloperAppName="someappname" | convert timeformat="%A" ctime(_time) AS date | stats count by date | sort -count

Tuesday 200
Monday 190
.
.
.
Saturday 20
Sunday 10

Above search is giving count based on day. But I am looking for avg count based on day. I tried adding avg before count but i didn't get any results

index="apigee" sourcetype="apigee:hec" | search DeveloperAppName="someappname" | convert timeformat="%A" ctime(_time) AS date | stats avg(count) by date | sort -count

I am looking for something like this

Tuesday 99% (add count of all tuesdays and divide by number of tuesday)
Monday 80%
..
...

Re: Average count based on day

Vijeta — Wed, 16 Jan 2019 19:06:16 GMT

You would do something like below , please rename variables as per your need

index="wineventlog" | eventstats count as C| eventstats count as D by Date|stats values(C) as C, values(D) as D by Date| eval per= D/C * 100

Re: Average count based on day

somesoni2 — Wed, 16 Jan 2019 19:07:37 GMT

Try like this

index="apigee" sourcetype="apigee:hec"  DeveloperAppName="someappname" 
| timechart span=1d count 
| convert timeformat="%A" ctime(_time) AS date | stats avg(count) as count by date | sort -count

Re: Average count based on day

th1agarajan — Wed, 16 Jan 2019 20:38:28 GMT

I am looking for something similar. Buy my search is not giving proper c value.

If I select seven days, then c value will be 1 (i.e. one week cannot have more than 1 Monday, 1 Tuesday....) If my duration is 2 weeks then C value should be 2. How to get proper c value.

I am getting proper D value. PFB my search.

Your help is appreciated.

Re: Average count based on day

Vijeta — Wed, 16 Jan 2019 20:57:00 GMT

Try this

index="apigee" sourcetype="apigee:hec" | search DeveloperAppName="someappname" | convert timeformat="%A" ctime(_time) AS week | eval date=strftime(_time,"%Y/%m%d")|  stats dc(date) as C, count as D by week| eval AVG= D/C`

Re: Average count based on day

th1agarajan — Wed, 16 Jan 2019 21:28:37 GMT

This is what I was looking for. Thank you Vijeta.