Summary Index - To calculate weekly average

Tester237 — Thu, 19 Oct 2023 11:25:26 GMT

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

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-vs-Today-in-a/m-p/657868/highlight/true#M17385

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

Re: Summary Index - To calculate weekly average

ITWhisperer — Thu, 19 Oct 2023 11:55:52 GMT

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

Re: Summary Index - To calculate weekly average

Tester237 — Thu, 19 Oct 2023 13:10:48 GMT

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 ?

Re: Summary Index - To calculate weekly average

ITWhisperer — Thu, 19 Oct 2023 13:41:02 GMT

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

Re: Summary Index - To calculate weekly average

Tester237 — Thu, 19 Oct 2023 14:24:18 GMT

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.

topic Re: Summary Index - To calculate weekly average in Splunk Search

Summary Index - To calculate weekly average

Re: Summary Index - To calculate weekly average

Re: Summary Index - To calculate weekly average

Re: Summary Index - To calculate weekly average

Re: Summary Index - To calculate weekly average