Report on timestamps for two different Eventtypes in the same search

[aramakrishnan]

I have two successful searches that I want to combine into one. Ideally, I'm trying to see for each segmentNo, what the process start time and process end time is, as well as duration. Both of the times are to be extracted from the timestamps for those events.

With the two searches below, I can create two independent tables which give me information on three columns: segmentNo, host and (start/end)time.

eventtype="processingstart" devID="XA123" segmentNo="*" |eval startTime=strftime(_time,"%Y-%m-%d %H:%M:%S")| dedup segmentNo |table host segmentNo startTime

AND

eventtype="processingend" devID="XA123" segmentNo="*" |eval endTime=strftime(_time,"%Y-%m-%d %H:%M:%S")| dedup segmentNo |table host segmentNo endTime

I tried using append and join (w/ combinations of search AND search NOT) but they would not let me search by eventtype. I basically want Splunk to understand that the startTime comes from the timestamp of eventtype1 and endTime comes from the timestamp of eventtype 2. If I take a long approach, I can export tables from both these searches independently and combine them in Excel or something. But I'm looking for one single table from Splunk that will report a table on these fields: segmentNo, host, startTime, endTime, duration

Any help would be appreciated 🙂

[ramdaspr · Accepted solution]

Have you tried Join

 eventtype="processingstart" devID="XA123" segmentNo="*" |eval startTime=strftime(_time,"%Y-%m-%d %H:%M:%S")| dedup segmentNo |table host segmentNo startTime| join segmentNo [search eventtype="processingend" devID="XA123" segmentNo="*" |eval endTime=strftime(_time,"%Y-%m-%d %H:%M:%S")| dedup segmentNo |table host segmentNo endTime]

should give you the result you are looking for.