Hi,
I'm trying to graph a daily weighted average of priority over time.
Data looks like: id=123,priority=80,time=50, user=guest.
Current query:
eval time=time/3600 | eventstats sum(time) as sum_time by user | eval weighted=time*priority/sum_time | timechart span=1d sum(weighted) by user limit=0
Issue: The sum_time field is totaling across all time instead of just the relevant day.
There doesn't seem to be an "addWeightedAverage" command similar to the "addTotals" command.
Is there a simple fix to this query I'm missing?
Thanks
Okay, now I understand the question. Try this:
... | eval tp = time * priority | bin span=1d _time | stats sum(time) as time sum(tp) as tp by _time user | eval weighted = tp / time | xyseries _time user weighted
Depending on the behaviour you want for missing data points you may want to replace the xyseries
with timechart span=1d sum(weighted) by user
or with timechart span=1d avg(weighted) by user
.
Okay, now I understand the question. Try this:
... | eval tp = time * priority | bin span=1d _time | stats sum(time) as time sum(tp) as tp by _time user | eval weighted = tp / time | xyseries _time user weighted
Depending on the behaviour you want for missing data points you may want to replace the xyseries
with timechart span=1d sum(weighted) by user
or with timechart span=1d avg(weighted) by user
.
Say an entire day has no data. xyseries
will not see that day, while timechart
will insert a row for that day.
Say a user has no data for a day. Depending on whether you want a zero or a null for that cell you'll want avg()
or sum()
.
Thanks a bunch! Although, I'm unclear how the result from the xyseries and the timechart/avg variant differ from each other - could you elaborate on that please? The only difference I see in the results is that timechart truncates without a limit=0 set.
I don't quite get what the desired result would look like. Do post a few sample events along with the result you're looking for, then we'll surely be able to help you come up with a search that automates this.
Sample data:
Day 1
id=1,time=1,priority=90,user=guest1
id=2,time=11,priority=10,user=guest1
id=3,time=4,priority=90,user=guest2
Day 2
id=4,time=1,priority=50,user=guest1
id=5,time=1,priority=10,user=guest1
id=6,time=99,priority=40,user=guest2
id=7,time=1,priority=100,user=guest2
Output:
Day 1:
guest 1 average weighted priority=16.67
guest 2 average weighted priority=90
Day 2:
guest 1 average weighted priority=30
guest 2 average weighted priority=40.6