Getting Data In

How to calculate the time between events based on a field change grouped by another field?

goodsellt
Contributor

I'm attempting to generate a table which shows the time between two consecutive login events for a user when the IP address of their system changes. I'd like to be able to sort the table by smallest and largest "time between events", where it is possible for a user to have more than one event (say during the time period measured they have three or more IPs used).

For example, a sample of my data would be:
timestamp | userid1 | ipaddress1
timestamp | userid2 | ipaddress2
timestamp | userid2 | ipaddress3
timestamp | userid1 | ipaddress4
timestamp | userid1 | ipaddress5
(simplified, as my search essentially only gives me login events and those 3 are the only fields that change)

I would like a table that would show:
userid1 | ipaddress1 (new ip) | ipaddress4 (old ip) | time between events (shortest)
userid2 | ipaddress2 (new ip) | ipaddress3 (old ip) | time between events
userid1 | ipaddress4 (new ip) | ipaddress 5 (old ip) | time between events (longest)

I've been playing around with eventstats (using this example (http://blogs.splunk.com/2013/10/31/streamstats-example/ as my guide since I'm newish to the Splunk platform), however, I'm never able to get this to work correctly unless I limit to a single userid. My results without the limiting it ends up having a table where it calculates every time the IP has changed (so for example I would get the difference between the first two events in my data and so on regardless of userid and I would have an table equal in length to my original data set).

I'm wondering if anyone with more experience has gotten something like that to work before in Splunk and you would be able to share some examples with me. Thank you!

1 Solution

wpreston
Motivator

You can accomplish this using either streamstats or transaction. First, here's a streamstats example:

... your base search ... | streamstats window=1 current=f last(_time) as newTime last(ipaddress) as newIP by userid | eval duration = newTime - _time | table userid newIP ipaddress duration

You can accomplish the same thing with transaction like this:

... your base search ... | transaction userid maxevents=2 mvlist=ipaddress | eval oldIP=mvindex(ipaddress, 0) | eval newIP=mvindex(ipaddress, 1) | table userid newIP oldIP duration

Something along those lines, anyway. Hope this helps out!

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try

your base search giving fields _time user ipaddress | sort user -_time | streamstats windo=1 current=f values(ipaddress) as old_ipaddress values(_time) as prev_time | eval duration=_time-prev_time | sort  duration

kreeger24
Engager

This worked perfectly. EDIT windoW=1

0 Karma

wpreston
Motivator

You can accomplish this using either streamstats or transaction. First, here's a streamstats example:

... your base search ... | streamstats window=1 current=f last(_time) as newTime last(ipaddress) as newIP by userid | eval duration = newTime - _time | table userid newIP ipaddress duration

You can accomplish the same thing with transaction like this:

... your base search ... | transaction userid maxevents=2 mvlist=ipaddress | eval oldIP=mvindex(ipaddress, 0) | eval newIP=mvindex(ipaddress, 1) | table userid newIP oldIP duration

Something along those lines, anyway. Hope this helps out!

goodsellt
Contributor

Yes thanks for this! I got the one using steamstats working correctly after finding that I needed to label my variables better. Just to clarify that the duration calculation would be in seconds though correct?

0 Karma

wpreston
Motivator

Glad this helped you out! And yes, the duration will be in seconds but you can eval it into minutes, hours or whatever else you might need.

0 Karma

goodsellt
Contributor

I did make a typo in this, I realized I wrote that I was using "eventstats" when actually I was using "streamstats" as per the example link.

0 Karma

goodsellt
Contributor

I am also attempting to use the transaction command to accomplish this as well, though I'm having issues understanding how I can use it with the constraint I cannot hardcode any of the fields into the "startswith" & "endswith".

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...