Comparing field with weekly average

[sk]

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

[scelikok]

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

[sk]

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!

[scelikok]

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

[sk]

@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?