How will I get second latest time after rex search?

[prateek_mishra]

I am using the below rex command and then finding out the latest and earliest time .

Search |rex ".*execution.date=(?\w+)" | rex "execution.date=(?\w+)}] and the following status: [COMPLETED]" |
stats earliest(_time) as First latest(_time) as Last by executiondate |

But instead of earliest time I need 2nd Latest time or penultimate time. How will I get? Thanks in Advance 🙂

[somesoni2]

Try like this

your current search with rex statements and before stats
| sort 2 -_time by executiondate
|stats earliest(_time) as First latest(_time) as Last by executiondate 

[DalJeanis]

your search that gets the records you want with _time and executiondate
| eventstats count as eventcount, latest(_time) as Last by executiondate
| where _time != Last OR eventcount=1
| stats earliest(_time) as First, latest(_time) as Penultimate, latest(Last) as Last by executiondate

The eventstats puts the Last figure where you can save it and get to it, on every record for each executiondate.

The where kills the very last date, but you already have saved what it said in Last.

The stats then calculates the First and Penultimate times. If there is only one event, then all three values are the same. If there are two, then First and Penultimate are the same.

[prateek_mishra]

I am gettting latest(last) time as penultimate time

[DalJeanis]

Be sure to mark your code as code, so the interface will not delete parts of it.