Splunk Search

How to get duration of transactions within the earliest and latest time?

limalbert
Path Finder

Hi all,

Below is how the data I have.

currentDate user _time
2017-02-01 aaa 8:00:00
2017-02-01 aaa 9:12:00
2017-02-01 aaa 11:15:00
2017-02-01 aaa 14:16:00
2017-02-01 aaa 17:00:00

As of now, I know how to find the duration between the earliest and the latest time (8:00:00 - 17:00:00).
mySearch
| stats earliest(_time) AS start last(_time) AS stop by currentDate, user
| eval durationHour = round(((stop - start)/3600),2)
| table currentDate user durationHour start stop

How do I create a query to find duration in between the earliest and the latest time in the format like below?
1. Duration between 8:00:00 and 9:12:00 --> NOTE: Duration between the earliest and the next earlier time
2. Duration between 9:12:00 and 11:15:00 --> NOTE: Treat the latest hour of the previous duration as the earliest time
3. Duration between 11:15:00 and 14:16:00
4. Duration between 14:16:00 and 17:00:00

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

Your Base Search Here | delta _time AS duration

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

|makeresults | eval raw="2017-02-01 aaa 8:00:00::2017-02-01 aaa 9:12:00::2017-02-01 bbb 8:00:00::2017-02-01 bbb 9:12:00::2017-02-01 ccc 11:15:00::2017-02-01 ccc 14:16:00::2017-02-02 aaa 7:00:00::2017-02-02 aaa 8:00:00"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "(?<currentDate>\S+)\s+(?<user>\S+)\s+(?<time>\S+)$"
| table time currentDate user

| rename COMMENT AS "Everything above fakes your data; everything below is your solution"

| eval _time = strptime(currentDate . " " . time, "%Y-%m-%d %H:%M:%S")
| sort 0 - _time
| streamstats global=f window=2 range(_time) AS duration BY user
0 Karma

woodcock
Esteemed Legend

Like this:

Your Base Search Here | delta _time AS duration
0 Karma

limalbert
Path Finder

Sorry. I wasn't clear on the question.

There are multiple dates, users. The delta won't work since it always subtracts different user from same day/ different day. How would I do this? I working on data with at least 5000 users

currentDate user _time
2017-02-01 aaa 8:00:00
2017-02-01 aaa 9:12:00
2017-02-01 bbb 8:00:00
2017-02-01 bbb 9:12:00
2017-02-01 ccc 11:15:00
2017-02-01 ccc 14:16:00
2017-02-02 aaa 7:00:00
2017-02-02 aaa 8:00:00

0 Karma

woodcock
Esteemed Legend

See new answer (you can unaccept and accept the other answer).

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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