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

[vinaybandaru]

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

[richgalloway · Accepted solution]

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.

[vinaybandaru]

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

[rmanrique]

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