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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...