How to obtain duration if the End Time is conditional?

[moinyuso96]

Currently my Splunk Search is shown as below:

Serial	Description	DateTime	StartTime	EndTime
MY111	Registration	2021-05-01 00:30:00	2021-05-01 00:30:00
MY122	Registration	2021-05-02 09:00:00	2021-05-02 09:00:00
MY134	Registration	2021-05-02 09:30:00	2021-05-02 09:30:00
MY122	Picking	2021-05-02 10:00:00		2021-05-02 10:00:00
MY134	Picking	2021-05-02 12:00:00		2021-05-02 12:00

However, there are some Serial that have not reached EndTime yet (only Registration description).

How I can get the duration (in seconds) for those serial that completed (Have both Registration & Picking description)

Expected Outcome:

Serial	Description	DateTime	StartTime	EndTime	Duration
MY111	Registration	2021-05-01 00:30:00	2021-05-01 00:30:00
MY122	Registration	2021-05-02 09:00:00	2021-05-02 09:00:00
MY134	Registration	2021-05-02 09:30:00	2021-05-02 09:30:00
MY122	Picking	2021-05-02 10:00:00		2021-05-02 10:00:00	3600
MY134	Picking	2021-05-02 09:40:00		2021-05-02 09:40:00	600

[ITWhisperer · Accepted solution]

| makeresults 
| eval _raw="Serial,Description,DateTime,StartTime,EndTime
MY111,Registration,2021-05-01 00:30:00,2021-05-01 00:30:00	 
MY122,Registration,2021-05-02 09:00:00,2021-05-02 09:00:00	 
MY134,Registration,2021-05-02 09:30:00,2021-05-02 09:30:00	 
MY122,Picking,2021-05-02 10:00:00,,2021-05-02 10:00:00
MY134,Picking,2021-05-02 12:00:00,,2021-05-02 09:40:00"
| multikv forceheader=1
| table Serial Description DateTime StartTime EndTime




| eval StartTime=strptime(StartTime,"%Y-%m-%d %H:%M:%S")
| eval EndTime=strptime(EndTime,"%Y-%m-%d %H:%M:%S")
| fieldformat StartTime=strftime(StartTime,"%Y-%m-%d %H:%M:%S")
| fieldformat EndTime=strftime(EndTime,"%Y-%m-%d %H:%M:%S")
| eventstats values(StartTime) as StartTime by Serial
| eval Duration=floor(EndTime-StartTime)