topic How to edit my search to graph a daily Weighted Average over time? in Splunk Search

How to edit my search to graph a daily Weighted Average over time?

asherman — Tue, 23 Dec 2014 21:26:56 GMT

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

Re: How to edit my search to graph a daily Weighted Average over time?

martin_mueller — Tue, 23 Dec 2014 22:23:30 GMT

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.

Re: How to edit my search to graph a daily Weighted Average over time?

asherman — Tue, 23 Dec 2014 22:42:16 GMT

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

Re: How to edit my search to graph a daily Weighted Average over time?

martin_mueller — Wed, 24 Dec 2014 00:39:33 GMT

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.

Re: How to edit my search to graph a daily Weighted Average over time?

asherman — Wed, 24 Dec 2014 00:51:56 GMT

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.

Re: How to edit my search to graph a daily Weighted Average over time?

martin_mueller — Wed, 24 Dec 2014 00:56:03 GMT

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().