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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...