topic Re: transaction calculate duration betweeen 2 events in Splunk Search

transaction calculate duration betweeen 2 events

preben12 — Wed, 12 Jul 2017 09:16:24 GMT

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.

Re: transaction calculate duration betweeen 2 events

HiroshiSatoh — Wed, 12 Jul 2017 09:36:04 GMT

In this case, what is the value of duration?

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

Re: transaction calculate duration betweeen 2 events

preben12 — Wed, 12 Jul 2017 10:22:28 GMT

then it's empty

Re: transaction calculate duration betweeen 2 events

preben12 — Wed, 12 Jul 2017 10:25:38 GMT

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

Re: transaction calculate duration betweeen 2 events

niketn — Wed, 12 Jul 2017 10:48:34 GMT

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"

Re: transaction calculate duration betweeen 2 events

preben12 — Wed, 12 Jul 2017 11:06:41 GMT

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

Re: transaction calculate duration betweeen 2 events

niketn — Wed, 12 Jul 2017 11:59:13 GMT

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.

Re: transaction calculate duration betweeen 2 events

preben12 — Wed, 12 Jul 2017 12:37:07 GMT

Thanks !! I managed to get the last bits together

Re: transaction calculate duration betweeen 2 events

niketn — Wed, 12 Jul 2017 13:56:31 GMT

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