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
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 ...