Splunk Search

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

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]) )

Tags (5)
1 Solution
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.
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.
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) )

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
| stats count as totalDetalle by detalexcep _time
| eval porc=totalDetalle/total*100
| table totalDetalle total porc``````
Get Updates on the Splunk Community!

#### .conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

#### Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

#### Troubleshooting the OpenTelemetry Collector

In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...