transaction calculate duration betweeen 2 events

[preben12]

I'm trying to use transactions to generate a timeline of events where the events are grouped by an eventId

I'm recieving up to 2 events as a START and a STOP event, and have to calculate the duration between them based on actualTime.

{
  "action" : "START",
  "source" : "AS_PLANNED",
  "timestamp" : "2017-07-12T10:07:14.682+02:00",
   "eventId" : "1366963140327",
  "title" : "sadfasdfasdf",
  "flowPublicationId" : "1366963137812",
  "timeAllocationType" : "Segment of program",
  "actualTime" : "2017-07-12T10:07:14.760+02:00",
  "startTimeAnnounced" : "2017-07-12T10:05:00.000+02:00",
  "startTimePlanned" : "2017-07-12T10:07:14.760+02:00",
  "stopTimePlanned" : "2017-07-12T10:12:50.360+02:00",
  "broadcastDate" : [ 2017, 7, 12 ],
  "live" : false,
  "quickReprise" : false,
  "streamingLive" : false,
  "streamingOD" : false,
  "numberOfBlocks" : "1",
  "blockPartNumber" : "1",
  "blockId" : "1366963138813"
}

{
  "action" : "STOP",
  "source" : "AS_PLANNED",
  "timestamp" : "2017-07-12T10:12:50.310+02:00",
  "eventId" : "1366963140327",
  "title" : "yyyyyy",
  "flowPublicationId" : "1366963137812",
  "timeAllocationType" : "Segment of program",
  "actualTime" : "2017-07-12T10:12:50.360+02:00",
  "startTimeAnnounced" : "2017-07-12T10:05:00.000+02:00",
  "startTimePlanned" : "2017-07-12T10:07:14.760+02:00",
  "stopTimePlanned" : "2017-07-12T10:12:50.360+02:00",
  "broadcastDate" : [ 2017, 7, 12 ],
  "live" : false,
  "quickReprise" : false,
  "streamingLive" : false,
  "streamingOD" : false,
  "numberOfBlocks" : "1",
  "blockPartNumber" : "1",
  "blockId" : "1366963138813"
}

The query I'm using ->

index="morpheus" 
| transaction eventId 
| eval start=if(action=="START",actualTime,startTimePlanned) 
| eval stop=if(action=="STOP",actualTime,stopTimePlanned) 
| eval duration=(strptime(stop,"%Y-%m-%dT%H:%M:%S,%3N") - strptime(start,"%Y-%m-%dT%H:%M:%S,%3N")) 
| table actualTime, action, title, start, stop, duration

But it seems that I'm not getting the duration correctly calculated.

[niketn · Accepted solution]

Please switch to stats instead see if following solves your issue. Transaction is using _time field for calculating duration which I feel is the timestamp field in your case. Following stats should perform better than stats and will give you control as to how you filter required events and calculate duration:

<YourBaseSearch>
| stats count as eventcount list(action) as action list(actualTime) as actualTime by eventId
| search eventcount=2 action="START" AND action="STOP"
| eval startTime=strptime(mvindex(actualTime,0),"%Y-%m-%dT%H:%M:%S.%3N%:z")
| eval endTime=strptime(mvindex(actualTime,1),"%Y-%m-%dT%H:%M:%S.%3N%:z")
| eval duration=endTime-startTime

Second line in the query will give you flexibility of finding matched events, unmatched events, multiple occurrences etc as per your data and use case. Following part of search is finding only those events which have both Start and Stop for the same eventId.

| search eventcount=2 action="START" AND action="STOP"

[preben12]

I actually need group with only 1 event to show up since this should indicate that something is missing/wrong.

But other that that the search seems to work when there is 2 events

[niketn]

If you want to find only events with START and not STOP, you need to change your search filter on the second line as explained in the answer: | search eventcount=1 action="START" AND action!="STOP"


The following finds all the events grouped by eventIds where only START is present but no STOP.

| stats count as eventcount list(action) as action list(actualTime) as actualTime by eventId
| search eventcount=1 action="START" AND action!="STOP"
| eval startTime=strptime(mvindex(actualTime,0),"%Y-%m-%dT%H:%M:%S.%3N%:z")
| eval endTime=now()
| eval duration=endTime-startTime

Setting | eval endTime=now() will tell that no STOP event found till now.

[preben12]

Thanks !! I managed to get the last bits together

[niketn]

@preben12, I am converting to Answer. Please accept if this helped you with your issue.

[HiroshiSatoh]

In this case, what is the value of duration?

| transaction eventId startswith="START" endswith="STOP" 

[preben12]

then it's empty

[preben12]

So duration is only calculated if there is at most 1 event. When the transaction returns 2 duration is empty