Splunk Search

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

harrychen
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)
0 Karma
1 Solution

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

View solution in original post

yannK
Splunk Employee
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

harrychen
Explorer

That's even sleeker, thanks!

0 Karma

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

View solution in original post

harrychen
Explorer

Awesome! That works.

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!