Splunk Search

transaction calculate duration betweeen 2 events

preben12
Communicator

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.

alt text

Tags (2)
1 Solution

niketn
Legend

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"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

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"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

preben12
Communicator

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

0 Karma

niketn
Legend

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

preben12
Communicator

Thanks !! I managed to get the last bits together

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

HiroshiSatoh
Champion

In this case, what is the value of duration?

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

preben12
Communicator

then it's empty

0 Karma

preben12
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...