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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...