Splunk Search

How to use the stored results in variables after stats command using by clause in calculation ?

vinaybandaru
Path Finder

Hi,

I'm using the below query in order retrieve average and standard deviation for the respective days (mon,tue,wed, etc. ) for each warehouse for the last 90 days and i want to use the output values in other calculation in order to retrieve the limits.

Could you please help how i can achieve this ? How are the values retrieved from stats command by clause are stored internally and how i can assign them to new variable matrix etc ?

Search Query:
index="orderstowh"
| where in (WH,1,2,3,4,5,6,7,8,9)
| timechart span=1d count as "Total" by WH
| untable _time WH Total
| eval dayofweek=strftime(_time,"%w")
| where dayofweek in (1,2,3,4,5)
| stats avg(Total) as avg stdev(Total) as stdev by WH,dayofweek
...........................
| eval lowerBound[WH,dayofweek]=(avg[WH,dayofweek]-(stdev[WH,dayofweek]) )
| eval upperBound[WH,dayofweek]=(avg[WH,dayofweek]+(stdev[WH,dayofweek]) )

Thanks in advance!!

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The results of the stats command are stored in fields named using the words that follow as and by. In your example, the results are in 'avg', 'stdev', 'WH', and 'dayofweek'. Each field is separate - there are no tuples in Splunk. Display the output from stats and you'll see there's a different row for each combination of 'WH' and 'dayofweek' so any evals will be calculated using those separate results. Therefore, you can use | eval lowerBound=avg-stdev, upperBound=avg+stdev.

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

The results of the stats command are stored in fields named using the words that follow as and by. In your example, the results are in 'avg', 'stdev', 'WH', and 'dayofweek'. Each field is separate - there are no tuples in Splunk. Display the output from stats and you'll see there's a different row for each combination of 'WH' and 'dayofweek' so any evals will be calculated using those separate results. Therefore, you can use | eval lowerBound=avg-stdev, upperBound=avg+stdev.

---
If this reply helps you, Karma would be appreciated.

vinaybandaru
Path Finder

Hi,

Thank you it works !!

index="orderstowh"
| where in (WH,1,2,3,4,5,6,7,8,9)
| timechart span=1d count as "Total" by WH
| untable _time WH Total
| eval dayofweek=strftime(_time,"%w")
| where dayofweek in (1,2,3,4,5)
| stats avg(Total) as avg stdev(Total) as stdev by WH,dayofweek
| eval lowerBound=(avg-(stdev) )
| eval upperBound=(avg+(stdev) )

0 Karma

rmanrique
Path Finder

I'm not sure that works. In my case it doesn't show anything and I'm using the same as you.

index=blueprism sourcetype=BP_Parkour_Status source=TABLA::RPA_BLUEPRISM 
| bin span=1d _time 
| stats count as total by _time
| where tipoexcep="Business Exception" 
| stats count as totalDetalle by detalexcep _time
| eval porc=totalDetalle/total*100
| table totalDetalle total porc
0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...