Search result based on max(field)

[th1agarajan]

Log format

ServiceName,ResponseTime,RequestTime,TransactionId
Service1,10,12,12345
Service2,5,8,12346
Service2,7,3,12347
Service1,8,25,12348
Service3,5,4,12349
Service2,10,2,12350

Expected result

Service Name	Count	Max(ResponseTime)	RequestTime	TransactionId
Service1	2	10	12	12345
Service2	3	10	2	12350
Service3	1	5	4	12349

I am able to get the service name,count and ResponseTime using the below search. But i need RequestTime and TransactionId based on Max(ResponseTIme). I need the RequestTime and TransactionId of the record which has Max(ResponseTime). Do i really need sub search to get the desired results?

my rex | stats count,max(ResponseTime) by ServiceName

[th1agarajan · Accepted solution]

The search should be

my rex |sort 0 - ResponseTime |stats count,max(ResponseTime) ,first(RequestTime),first(TransactionId) by ServiceName

If your search returns more than 10,000 results then add | sort 0 field
sort command will truncate the output to 10000 rows.
results will be automatically limited to 10000 if you don't specify

| sort 0 field

[HiroshiSatoh]

Why do not you try to sort?

my rex |sort ServiceName,- ResponseTime |stats count,max(ResponseTime) ,first(RequestTime),first(TransactionId) by ServiceName

[th1agarajan]

This is exactly i was looking for. Thank you very much HiroshiSatoh.