Splunk Search

Identify elapsed time from two records sharing the same key

mariraj
New Member

The input data looks like below.

Req_no|Type|Time
1000|Request|2019-04-10T11.21.46.455Z
1000|Response|2019-04-10T11.22.46.466Z
2000|Request|2019-04-10T11.01.46.455Z
2000|Response|2019-04-10T11.02.06.466Z

I am trying to calculate the elapsed time (round off to seconds) between the request and response. The expected output should be as follows.
Req_no|Elapsed_Time
1000|60
2000|20

Appreciate your help with this.

0 Karma
1 Solution

adonio
Ultra Champion

Try this search anywhere, and apply the same formula on your data.
note: there are many other ways to accomplish this goal

| makeresults count=1
| eval data="1000|Request|2019-04-10T11.21.46.455Z;;;1000|Response|2019-04-10T11.22.46.466Z;;;2000|Request|2019-04-10T11.01.46.455Z;;;2000|Response|2019-04-10T11.02.06.466Z;;;3000|Request|2019-04-10T11.21.46.455Z;;;3000|Response|2019-04-10T11.22.46.466Z;;;4000|Request|2019-04-10T11.01.46.455Z;;;4000|Response|2019-04-10T11.02.06.466Z"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<Req_no>[^\|]+)\|(?<Type>[^\|]+)\|(?<Time>[^\|]+)"
| table Req_no Type Time
| rename COMMENT as "the above generates data below is the solution" 
| eval epoch_time = strptime(Time, "%Y-%m-%dT%H.%M.%S.%3N")
| eval req_time = if(Type=="Request",epoch_time,null())
| eval res_time = if(Type=="Response",epoch_time,null())
| stats values(req_time) as request_time values(res_time) as response_time by Req_no
| eval Elapsed_Time = round(response_time - request_time)

hope it helps

View solution in original post

0 Karma

adonio
Ultra Champion

Try this search anywhere, and apply the same formula on your data.
note: there are many other ways to accomplish this goal

| makeresults count=1
| eval data="1000|Request|2019-04-10T11.21.46.455Z;;;1000|Response|2019-04-10T11.22.46.466Z;;;2000|Request|2019-04-10T11.01.46.455Z;;;2000|Response|2019-04-10T11.02.06.466Z;;;3000|Request|2019-04-10T11.21.46.455Z;;;3000|Response|2019-04-10T11.22.46.466Z;;;4000|Request|2019-04-10T11.01.46.455Z;;;4000|Response|2019-04-10T11.02.06.466Z"
| makemv delim=";;;" data
| mvexpand data
| rex field=data "(?<Req_no>[^\|]+)\|(?<Type>[^\|]+)\|(?<Time>[^\|]+)"
| table Req_no Type Time
| rename COMMENT as "the above generates data below is the solution" 
| eval epoch_time = strptime(Time, "%Y-%m-%dT%H.%M.%S.%3N")
| eval req_time = if(Type=="Request",epoch_time,null())
| eval res_time = if(Type=="Response",epoch_time,null())
| stats values(req_time) as request_time values(res_time) as response_time by Req_no
| eval Elapsed_Time = round(response_time - request_time)

hope it helps

0 Karma

mariraj
New Member

Works like a charm. Thanks for your help with it.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...