Splunk Search

Get rows from the intermediate table by max value of a column

Explorer

I have an intermediate table from some query:

... | table Stock_price_difference, start_time, end_time, company

Stock_price_differencestart_timeend_timecompany
1.310:0110:20Apple
0.311:0011:05Apple
1.111:3011:35Apple
0.409:1009:18MS
0.809:5010:00MS
0.110:0010:10MS
110:1510:20Amazon
1.111:1011:15Amazon

I would like to get the rows by max stock price difference for each company:

Stock_price_differencestart_timeend_timecompany
1.310:0110:20Apple
0.809:5010:00MS
1.111:1011:15Amazon

How can I do that?

Thanks.

Tags (1)
1 Solution
Revered Legend

This should solve the problem:

``````..| table Stock_price_difference, start_time, end_time, company|sort - company,Stock_price_difference| eval CountF=1|streamstats sum(CountF) as CountF by company|where CountF<2 | fields - CountF
``````

This should give you top 1 Stock_price_difference for each company along with start_time and end_time field (or any field available)

Splunk Employee

They are many approaches, here is the quick and hack :
- using a sort and a dedup on the company, to keep the event highest value in the Stock-price-difference per company.

`...| sort company -Stock_price_difference | dedup company | table Stock_price_difference, start_time, end_time, company`

result is :
```Stock_price_difference start_time end_time company 1.1 11:10 11:15 Amazon 1.3 10:01 10:20 Apple 0.8 09:50 10:00 MS ```

Explorer

That's even sleeker, thanks!

Revered Legend

This should solve the problem:

``````..| table Stock_price_difference, start_time, end_time, company|sort - company,Stock_price_difference| eval CountF=1|streamstats sum(CountF) as CountF by company|where CountF<2 | fields - CountF
``````

This should give you top 1 Stock_price_difference for each company along with start_time and end_time field (or any field available)

Explorer

Awesome! That works.

.conf21 Now Fully Virtual!