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, an upvote 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, an upvote 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, an upvote would be appreciated.
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...