Splunk Search

How to calculate start and end time from two different searches

kaphie2002
New Member

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

Tags (1)
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

kaphie2002
New Member

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!

0 Karma

kaphie2002
New Member

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

Thanks

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

kaphie2002
New Member

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

kmaron
Motivator

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)
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...