Splunk Search

## Identify elapsed time from two records sharing the same key

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.

Tags (2)
1 Solution
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

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

New Member

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

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 ...