Splunk Search

How to fetch the corresponding value from the data which have minimum, maximum and current value for number?

srujana96
Explorer

i have the below data,

dc_number argosweekstart total_forecast
610 2022-10-23 23534.000003657507
610 2022-05-22 457659.9999990086
610 2022-06-19 457026.96672087134
610 2022-06-12  499736.9999989038


i have fetched the below table which have maximum minimum and current data corresponding to the number from the below query
index="index" 
|stats min(total_forecast) as Minimum max(total_forecast) as Maximum latest(total_forecast) as Current by dc_number
|table dc_number week_min Minimum week_max Maximum week_cur Current

dc_number week_min Minimum week_max Maximum week_cur Current
610   23534.000003657507   499736.999998903800   23534.000003657507

 

but i am expecting the below output with corresponding week value from the first table. that means, for week_min it should pick it's corresponding value from week from the minimum value and same from maximum and current. below is the expected output.

dc_number week_min Minimum week_max Maximum week_cur Current
610 2022-10-23 23534.000003657507 2022-06-12 499736.999998903800 2022-10-23 23534.000003657507
Labels (3)
Tags (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The stats command is a transforming one.  That means the only fields in the output are those mentioned in the command - in this case Minimum, Maximum, Current and dc_number.  You can use eventstats to calculate Minimum, Maximum, and Current without losing other fields.  Then you can do some manipulations to get the final result.

| makeresults | eval _raw="dc_number	argosweekstart	total_forecast
610	2022-05-22	457659.9999990086
610	2022-10-23	23534.000003657507
610	2022-06-19	457026.96672087134
610	2022-06-12	499736.9999989038"
| multikv forceheader=1
```Above just sets up test data```
```Compute min, max, and current values```
| eventstats min(total_forecast) as Minimum, max(total_forecast) as Maximum, 
latest(total_forecast) as Current by dc_number
```Find the week number that matches min, max, and current```
| eval week_cur=if(total_forecast=Current, argosweekstart, null()), week_max=if(total_forecast==Maximum, argosweekstart, null()), week_min=if(total_forecast=Minimum, argosweekstart, null())
| table dc_number week_min Minimum week_max Maximum week_cur Current
```Merge the results into a single row for each dc```
| stats values(*) as * by dc_number
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The stats command is a transforming one.  That means the only fields in the output are those mentioned in the command - in this case Minimum, Maximum, Current and dc_number.  You can use eventstats to calculate Minimum, Maximum, and Current without losing other fields.  Then you can do some manipulations to get the final result.

| makeresults | eval _raw="dc_number	argosweekstart	total_forecast
610	2022-05-22	457659.9999990086
610	2022-10-23	23534.000003657507
610	2022-06-19	457026.96672087134
610	2022-06-12	499736.9999989038"
| multikv forceheader=1
```Above just sets up test data```
```Compute min, max, and current values```
| eventstats min(total_forecast) as Minimum, max(total_forecast) as Maximum, 
latest(total_forecast) as Current by dc_number
```Find the week number that matches min, max, and current```
| eval week_cur=if(total_forecast=Current, argosweekstart, null()), week_max=if(total_forecast==Maximum, argosweekstart, null()), week_min=if(total_forecast=Minimum, argosweekstart, null())
| table dc_number week_min Minimum week_max Maximum week_cur Current
```Merge the results into a single row for each dc```
| stats values(*) as * by dc_number
---
If this reply helps you, Karma would be appreciated.
0 Karma

somesoni2
Revered Legend

Give this a try

index="YourIndex"
| eventstats min(total_forecast) as min max(total_forecast) as max latest(total_forecast) as current by dc_number
|stats values(eval(if(total_forecast=min,argosweekstart,null()))) AS week_min min(total_forecast) AS Minimum  values(eval(if(total_forecast=max,argosweekstart,null()))) AS week_max max(total_forecast) AS Maximum values(eval(if(total_forecast=current,argosweekstart,null()))) AS week_cur latest(total_forecast) AS Current by dc_number 

srujana96
Explorer

Thank you @somesoni2 . it worked!!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...