Splunk Search

Calclute Avg of max values for a given period

x186855
New Member

Hi everyone,
please help me in below task , appreciate your time and effort
Use case : in below table for example we have to achieve the “ Avg=8.5”
4,6,7,10 are the max values among 24 hours each day

Day Max value

day 1 =4
day 2 =6
day 3 =7

day 4 =10

Avg = 6.75

Tags (1)
0 Karma

niketn
Legend

Please revisit your question. Data in your question seem to be 4, 6, 7 and 10 which will give average as 6.75. Is 8.5 also part of your data (may be on day 5)? Even then average will not be 8.5. Please give a rationale behind average of 8.5. Also please add your field names/raw data (dummy/anonymize data if you have to)

Following is run anywhere search based on your data which gives average 6.5:

| makeresults
| eval Day="day 1"
| eval Daily_Max_Erlang_Value=4
| append [| makeresults
          | eval Day="day 2"
          | eval Daily_Max_Erlang_Value=6]
| append [| makeresults
          | eval Day="day 3"
          | eval Daily_Max_Erlang_Value=7]
| append [| makeresults
          | eval Day="day 4"
          | eval Daily_Max_Erlang_Value=10]
| table Day Daily_Max_Erlang_Value
| stats avg(Daily_Max_Erlang_Value) as Avg

Final stats pipe is the only command you need. Everything above it dummy's data as per your question.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

x186855
New Member

Thanks! niket for the effort, my bad i have corrected the avg value to 6.75, below is the updated query which is not giving me the desired results when compareted with another Tool'ss output which use the same database

| bucket _time span=1d |eval Erlangs=(SCSCFACCUMULATEDORIGINATINGCALLTIME+SCSCFACCUMULATEDTERMINATINGCALLTIME)/3600|eventstats max(Erlangs) as maxerl by _time | stats avg(maxerl) by g3ManagedElement

walkthrough

|bucket _time span=1d

Purpose: bucketing the period as 1 day


|eval Erlangs=(SCSCFACCUMULATEDORIGINATINGCALLTIME+SCSCFACCUMULATEDTERMINATINGCALLTIME)/3600

Purpose : formula calculation


|eventstats max(Erlangs) as maxerl by _time

Purpose: extracting max value in 1d(24hours)


|stats avg(maxerl) by g3ManagedElement

Purpose: averaging the max values

0 Karma

niketn
Legend

Based on the details, you seem to capture daily max Erlang and then compute the average over days. First of you do not need eventstats. Secondly stats command will not include bins with no values (which implies some of the days might get missing, for examples assume that on Sundays you dont receive a call hence Erlang will be 0). If you want to include 0 Erlang bucket/s in your average calculation you can use timechart instead and fillnull buckets with 0, so that they account for average calculation.

<YourBaseSearch>
| eval Erlangs=
 (SCSCFACCUMULATEDORIGINATINGCALLTIME + SCSCFACCUMULATEDTERMINATINGCALLTIME) / 3600
| timechart span=1d max(Erlangs) as maxer1
| fillnull value=0 maxer1
| stats avg(maxer1) as Avg

Can you please add details for by g3ManagedElement. What does this field contain? Do you want to calculate max by all g3ManagedElements and then average specific to individual g3ManagedElements? Please confirm, as this one might require eventstats.

If by g3ManagedElements is applicable to both Max and Avg calculation then you can use the following search:

<YourBaseSearch>
| timechart span=1d max(date_second) as Max by g3ManagedElement
| fillnull value=0
| fields - _time _span
| rename * as Max*
| eval buckets=1
| addcoltotals
| tail 1
| foreach Max* [eval <<FIELD>>=round(<<FIELD>>/buckets,2)]
| rename Max* as Avg*
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
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 ...