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