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!

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

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

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...