topic Re: Comparing field with weekly average in Splunk Search

Comparing field with weekly average

sk — Thu, 04 Mar 2021 00:51:23 GMT

Hi,

I am trying to compare a field (Job duration) with its weekly average. Something is wrong with my join. It is returning only the first row's values from the main search.

Here is the query:

index="n"
| stats values(Job) by Date, Duration, Status
| join lower(Job) max=0
[ search
index="n" earliest=-8d
| stats count(eval(if( Date>relative_time(now(),"-d"),NULL,1))) as weekly_total, sum(eval(if(Date>relative_time(now(),"-d"), 0,Duration))) AS total_duration by Job
| eval Weekly_Avg=(total_duration/weekly_total)
]
| table Job, Duration, Weekly_Avg, Status
| dedup Job

Data:

Job Duration Date Status
A 5 2021-03-03 Success
B 9 2021-03-03 Failed
A 5 2021-03-02 Success
B 8 2021-03-02 Success
A 6 2021-03-01 Success
B 7 2021-03-01 Success

What I want:

Job Duration Weekly Avg Status
A 5 5.5 Success
B 9 7.5 Failed

What I get:

Job Duration Weekly Avg Status
A 5 5.5 Success
B 5 (from JobA) 7.5 Success (from Job A)

** Edit: I am finding there are duplicate rows in my data (exactly same data) which is also not helping.

TIA

Re: Comparing field with weekly average

scelikok — Thu, 04 Mar 2021 07:52:15 GMT

Hi @sk,

Can you try below streamstats?

index="n" | streamstats avg(Duration) as Weekly_Avg by Job time_window=7d global=f current=t | dedup Job | table Job Duration Weekly_Avg Status

Re: Comparing field with weekly average

sk — Thu, 04 Mar 2021 20:52:35 GMT

@scelikok Thanks for the info about streamstats.

This reports the duration and weekly_avg as the same value so it's as if the time_window is being ignored, for some reason. Any clue?

Re: Comparing field with weekly average

scelikok — Fri, 05 Mar 2021 05:05:18 GMT

Hi @sk,

Is it possible that you missed setting time range for more than one week?

index="n" earliest=-8d | streamstats avg(Duration) as Weekly_Avg by Job time_window=7d global=f current=t | dedup Job | table Job Duration Weekly_Avg Status

Re: Comparing field with weekly average

sk — Fri, 05 Mar 2021 17:37:31 GMT

Hi @scelikok ,

I was missing the "earliest=-8d" but no difference in the result. I had to use global=t since the error says false can only be used with windows not time_window's. I'll continue to play around. Thanks for your help!