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