Splunk Search

How to obtain duration if the End Time is conditional?

moinyuso96
Path Finder

Currently my Splunk Search is shown as below:

SerialDescriptionDateTimeStartTimeEndTime
MY111Registration2021-05-01 00:30:002021-05-01 00:30:00 
MY122Registration2021-05-02 09:00:002021-05-02 09:00:00 
MY134Registration2021-05-02 09:30:002021-05-02 09:30:00 
MY122Picking2021-05-02 10:00:00 2021-05-02 10:00:00
MY134Picking2021-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:

SerialDescriptionDateTimeStartTimeEndTimeDuration
MY111Registration2021-05-01 00:30:002021-05-01 00:30:00  
MY122Registration2021-05-02 09:00:002021-05-02 09:00:00  
MY134Registration2021-05-02 09:30:002021-05-02 09:30:00  
MY122Picking2021-05-02 10:00:00 2021-05-02 10:00:003600
MY134Picking2021-05-02 09:40:00  2021-05-02 09:40:00600
Labels (1)
0 Karma
1 Solution

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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| 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)
Get Updates on the Splunk Community!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...