topic Re: How to calculate start and end time from two different searches in Splunk Search

How to calculate start and end time from two different searches

kaphie2002 — Thu, 17 May 2018 16:55:52 GMT

Hello,

  I am trying to calculate the total time it takes for a request to be processed. I have two searches, the first search display the start time and the second search displays the end time. Now I need to create a table or chart that shows the duration for each request.

Start time search - host=request c.m.p.t.ThumbnailProcessor | rex field=_raw ".jpg.(?.).\"code" | table RequestID _time

End time search - host=images Successfully rendered | rex field=_raw ".images//(?.).jpg" | table RequestID _time

How do I combine these two searches?

I tried using append to combine both search results but I was only able to get the results i.e time and request ID from one search and not both.

Thanks,
Kafayat

Re: How to calculate start and end time from two different searches

kmaron — Thu, 17 May 2018 17:25:37 GMT

I believe there are other, less expensive, ways to do this but I do it using transaction.

You can do something like this:

(host=request OR host=images) ("c.m.p.t.ThumbnailProcessor" OR "Successfully rendered")  <- or however to combine your data
| eval Start_Time = case(host="request",_time)
| eval End_time = case(host="images",_time)
| transaction RequestID  (fill in maxspan, etc that fits your query)
| eval Elapsed_Time = (End_time - Start_Time)

Re: How to calculate start and end time from two different searches

somesoni2 — Thu, 17 May 2018 18:11:48 GMT

Give this a try

(host=*request* c.m.p.t.ThumbnailProcessor)  OR ( host=*images* Successfully rendered) 
| rex field=_raw ".*jpg.(?<RequestID1>.*).*\"code*"  | rex field=_raw ".*images//(?<RequestID2>.*).jpg" 
| eval RequestID=coalesce(RequestID1,RequestID2) | eval host=if(match(host,"request"),"Start","End")
| chart values(_time) over RequestID by host | eval Duration=End-Start

Re: How to calculate start and end time from two different searches

kaphie2002 — Fri, 18 May 2018 15:07:07 GMT

Hello Somesoni2,

   Thanks for your response, I think I'm pretty close. I used the query above but I am not getting the Duration in the output. The table shows 2 entries for each requestID one for Start and another for End but I can't compute the difference. I tried using the table command to show to values for Duration but it was blank. I am quite new to splunk so I don't know where to go from here. Thanks

RequestID Start End
123456 1526654924

123456 1526654930
78965 1526654945

78965 1526654970
654321 1526654985

654321 152665500

Thanks again!

Re: How to calculate start and end time from two different searches

kaphie2002 — Fri, 18 May 2018 15:08:49 GMT

Oh no the formatting is bad. the first requestID shows the start time while the second shows the end time.

Thanks

Re: How to calculate start and end time from two different searches

somesoni2 — Fri, 18 May 2018 15:41:16 GMT

Strange. The host values should be mutually excluding for events from host request and images and there should've been just one row per RequestID. See if this variation works.

(host=*request* c.m.p.t.ThumbnailProcessor)  OR ( host=*images* Successfully rendered) 
 | rex field=_raw ".*jpg.(?<RequestID1>.*).*\"code*"  | rex field=_raw ".*images//(?<RequestID2>.*).jpg" 
 | eval RequestID=coalesce(RequestID1,RequestID2) 
 | eval Start=if(match(host,"request"),_time, null())
 | eval End=if(match(host,"images"),_time, null())
 | stats values(Start) as Start values(End) as End by RequestID | eval Duration=End-Start

Re: How to calculate start and end time from two different searches

kaphie2002 — Fri, 18 May 2018 18:35:53 GMT

There were spaces around the RequestID, I used the trim command to remove the spaces and it worked perfectly. Thanks a lot for your help!

One last thing is there an easy way to group the duration in a bar chart?
For example I will like to group the requests by 5min spans i.e show the total number of requests that take 0-5 mins, 5-10 mins 10-15 mins etc.

Re: How to calculate start and end time from two different searches

somesoni2 — Fri, 18 May 2018 18:43:25 GMT

Sure. Just add this to your currently working search. Duration is right now showing in secs, so we'll convert it to minutes, then bin it in 5 min buckets and then a stats command.

your current search | eval Duration=round(Duration/60) | bin span=5 Duration
| stats count by Duration