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

[goodsellt]

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!

[somesoni2]

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]

This worked perfectly. EDIT windoW=1

[wpreston · Accepted solution]

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]

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?

[wpreston]

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.

[goodsellt]

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.

[goodsellt]

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