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
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
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!
Oh no the formatting is bad. the first requestID shows the start time while the second shows the end time.
Thanks
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
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.
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
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)