Summary Index - To calculate weekly average

[Tester237]

Hi Team, 

I'm using summary index for below requirement :
1. Store daily counts of HTTP_Status_Code per hour for each of the application (app_name) on to daily summary index
2. Once in a week, calculate the average for each app_name by hour, HTTP_STATUS_CODE for the stored values in daily summary index. 
3. This average values will be showed in dashboard widget. 

But when I'm trying to calculate avg for the stored values, it isn't working. Below are the steps I'm following:

1. Pushing HTTP_Status_Code, _time,hour, day, app_name, count along with value "Summary_test" (for ease of filtering) to daily index named "summary_index_1d". Note : app_name is a extracted field. There are 25+ different values

index="index" 
| fields HTTP_STATUS_CODE,app_name 
| eval HTTP_STATUS_CODE=case(like(HTTP_STATUS_CODE, "2__"),"2xx",like(HTTP_STATUS_CODE, "4__"),"4xx",like(HTTP_STATUS_CODE, "5__"),"5xx")
| eval hour=strftime(_time, "%H")
| eval day=strftime(_time, "%A")
| bin _time span=1d
| stats count by HTTP_STATUS_CODE,_time,hour,day,app_name
| eval value="Summary_Test" | collect index=summary_index_1d 

2. Retrieve data from summary index. its showing up the data pushed

index=summary_index_1d "value=Summary_Test"

 3. Now I want to calculate the average for previous 2 or 4 weekday data stored in summary index. I'm using below as reference 

https://community.splunk.com/t5/Splunk-Enterprise/How-to-Build-Average-of-Last-4-Monday-Current-day-...

Trying to perform avg on summary index stored values. But this fails

index=summary_index_1d "value=Summary_Test" app_name=abc HTTP_STATUS_CODE=2xx
| eval current_day = strftime(now(), "%A") 
| eval log_day = strftime(_time, "%A") 
| eval hour=strftime(_time, "%H")
| eval day=strftime(_time, "%d")| eval dayOfWeek = strftime(_time, "%u") | where dayOfWeek >=1 AND dayOfWeek <= 5
| stats count as value by hour log_day day  | sort log_day, hour | stats avg(value) as average by log_day,hour

I guess the "hour" in the query is creating conflict. I tried without it and also by changing the values, but not returning expected result. When the same query is used on main index, it works perfectly fine for my requirement. But when used on summary index, its not able to calculate the average. 

This works fine for the requirement. But when same is applied on "Summary index", it fails

index=index app_name=abc 
| eval HTTP_STATUS_CODE=case(like(status, "2__"),"2xx") 
| eval current_day = strftime(now(), "%A") 
| eval log_day = strftime(_time, "%A") 
| eval hour=strftime(_time, "%H")
| eval day=strftime(_time, "%d")| eval dayOfWeek = strftime(_time, "%u") | where dayOfWeek >=1 AND dayOfWeek <= 5
| stats count as value by hour log_day day  | sort log_day, hour | stats avg(value) as average by log_day,hour

Can you please help me understand what's wrong with query used on summary index ? 

@ITWhisperer @yuanliu @smurf  

[Tester237]

Yes. This is the one. In results, just 00 is being listed in hour column. 

how can this be resolved to achieve results similar to main index ? 

[ITWhisperer]

Try removing the line - hour should already be coming through from the summary index

[Tester237]

Thank you @ITWhisperer. 

I was running stats again to capture count which was already present in the data along with hour as mentioned by you

Here is final query :

index=summary_index_1d "value=Summary_test" app_name=abc HTTP_STATUS_CODE=2xx
| eval current_day = strftime(now(), "%A") 
| eval log_day = strftime(_time, "%A") 
| eval day=strftime(_time, "%d")| eval dayOfWeek = strftime(_time, "%u") | where dayOfWeek >=1 AND dayOfWeek <= 5
| stats avg(count_value) by log_day,hour,day

Let me know if any other changes are required on query which can improve its performance. 
Thanks Again.  

[ITWhisperer]

Perhaps it is this line?

| eval hour=strftime(_time, "%H")

The _time value here will be the time for the start of the day when the summary index was updated i.e. the hour will always be 00