Splunk Search

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

asherman
Path Finder

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

1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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.

martin_mueller
SplunkTrust
SplunkTrust

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

asherman
Path Finder

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma

asherman
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...