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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...