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!

#### Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

#### Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

#### Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...