transaction creates multi value field help to get just the most recent when out of order

[kmaron]

I'm trying to create a timeline of events and I'm running into an issue when certain steps are repeated and the data is out of order.

My data originally comes from DB Connect so this is the format:
Batch# Step# StartTime EndTime

I'm trying to combine them into a transaction so I can get times between each step as well as duration of each step. It works great when I only have a single Step1 but when I have more than one I'm running into issues.

Since the data is coming from DBConnect the records don't show up in the order of actual time they happened.

This is essentially how things show up out of order:
_time Batch Start_Time
2018-04-30 20:01:21.100 Batch1 Step1 2018-04-30 19:00:18.0
2018-04-30 21:01:20.939 Batch1 Step1 2018-04-30 20:00:58.0
2018-04-30 21:01:20.939 Batch1 Step3 2018-04-30 20:53:05.0
2018-04-30 21:01:20.939 Batch1 Step1 2018-04-30 20:52:18.0
2018-04-30 21:01:20.939 Batch1 Step2 2018-04-30 20:52:20.0

I can get things together but then I have a multivalue field with any number of values.
Step1 2018-04-30 19:00:18.0 Step2 2018-04-30 20:52:20.0 Step3 2018-04-30 20:53:05.0
Step1 2018-04-30 20:00:58.0
Step1 2018-04-30 20:52:18.0

This is what I want:
Step1 2018-04-30 20:52:18.0 Step2 2018-04-30 20:52:20.0 Step3 2018-04-30 20:53:05.0

I have my search set up which captures all of the data but I still end up with 3 start times for step 1. When I try to add a startswith to my transaction it breaks things apart incorrectly and I can't use an endswith to fix it because the end changes.

This is the search I have currently:

index=foo source=bar 
| eval Step1_Start = case(Task_Name="Step1",Start_Time)
| eval Step2_Start = case(Task_Name="Step2",Start_Time)
| eval Step3_Start = case(Task_Name="Step3",Start_Time)
| transaction Batch_Id 
| table Batch_Id Task_Name Step1_Start Step2_Start Step3_Start

I'm not sure if I need to collect the data differently or work from the multi-value field. Any help/guidance would be appreciated.

[somesoni2 · Accepted solution]

Assuming a Batch_Id - Task_Name combination is uniq, give this a try

index=foo source=bar 
| eval Start_Time=strptime(Start_Time,"%Y-%m-%d %H:%M:%S.%N")
 | eval Step1_Start = case(Task_Name="Step1",Start_Time)
 | eval Step2_Start = case(Task_Name="Step2",Start_Time)
 | eval Step3_Start = case(Task_Name="Step3",Start_Time)
 | stats max(*_Start) as *_Start by Batch_Id Task_Name
| foreach *_Start [| eval <<FIELD>>=strftime('<<FIELD>>',"%Y-%m-%d %H:%M:%S.%N")]
 | stats list(*) as * by Batch_Id 

OR

index=foo source=bar 
| dedup Batch_Id Task_Name
 | eval Step1_Start = case(Task_Name="Step1",Start_Time)
 | eval Step2_Start = case(Task_Name="Step2",Start_Time)
 | eval Step3_Start = case(Task_Name="Step3",Start_Time)
 | transaction Batch_Id 
 | table Batch_Id Task_Name Step1_Start Step2_Start Step3_Start

[kmaron]

dedup isn't 100% since my data from DB Connect can have more than one of the same task. BUT it's close.

I LOVE the the first one that doesn't use transaction at all. Thank you @somesoni2!