Splunk Search

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

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

Esteemed Legend

Like this:

Your Base Search Here | delta _time AS duration

View solution in original post

0 Karma

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

Esteemed Legend

Like this:

Your Base Search Here | delta _time AS duration

View solution in original post

0 Karma

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

Esteemed Legend

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!