How to search the duration between the time a user logged in to a host and the time another user logged in to the same host?

[swannie]

I'm having a hard time wrapping my head around this, and after a few false-starts, I'm hoping the community can point me in the right direction. My goal is to search some logs by User ID and show the time each user was logged into a host (identified by IP address). The only way to calculate when a user stopped using a host is when a different user logs into the same host, thus starting the timer for them. Unfortunately, a logout event doesn't exist, so the time I'm trying to show is basically the time until there was another login to the same IP address. The logs look something like this, I've numbered the events for easy ID in the text below:

1: 2016-09-06 10:00:00 ... user_name=jsmith  ip=10.10.10.10
2: 2016-09-06 10:20:00 ... user_name=bob     ip=10.10.10.10
3: 2016-09-06 10:25:00 ... user_name=jsmith  ip=10.10.10.5
4: 2016-09-06 10:30:00 ... user_name=sue     ip=10.10.10.10
5: 2016-09-06 10:40:00 ... user_name=jsmith  ip=10.10.10.10
6: 2016-09-06 10:50:00 ... user_name=bob     ip=10.10.10.5
7: 2016-09-06 11:00:00 ... user_name=bob     ip=10.10.10.10

What I want to do is search based on a username and end up with a result table that looks like this:

Login time/duration for jsmith:
Start Time              Duration (Mins)    Username    IP Address
2016-09-06 10:00:00     20                 jsmith      10.10.10.10 [difference between events 1 and 2]
2016-09-06 10:25:00     25                 jsmith      10.10.10.5   [difference between events 3 and 6]
2016-09-06 10:40:00     20                 jsmith      10.10.10.10 [difference between events 5 and 7]

What I need to do is base jsmith's duration on the time from his login to the time someone else logs into the same host, but I need to do that searching just on the username - which obviously doesn't return the logins for other users. I've tried transactions, basically searching for username=* and then adding a where to the search, but the problem here is that it groups all of the jsmith/10.10.10.10 logs into a single item showing that he was logged in for 60 minutes (10:00 to 11:00).

Next I tried to sort the data based on _time and ip, and do a delta on it which was my closest success so far, but the problem is that the delta will return a time between the last entry for 10.10.10.10 and the first entry for 10.10.10.5, which is a false result, so I need some way to say "only do a delta when the same value is in this field" ... which doesn't seem to exist. 🙂

If I was doing this in a script of some kind it would be a multi-step process where I would query for all IP addresses that jsmith used, feed those into a loop and get the user names that logged into each IP address and do my calculations based on that, but I'm assuming there's a better, more splunky way to do it?

... I hope that all made sense. 🙂

Thanks in advance!
Brian

[somesoni2]

Try something like this

your base search giving field _time user ip | sort 0 ip -_time| streamstats current=f window=1 values(_time) as prev_time values(user) as prev_user by ip | where isnotnull(prev_time) AND user="PutUserFilterHere" | eval Duration=tostring(prev_time-_time,"duration") | eval "Start Time" =strftime(_time,"%m/%d/%Y %H:%M:%S") | table "Start Time", Duration user ip 

[sundareshr · Accepted solution]

Try this

| sort ip | streamstats window=1 current=f last(user_name) as next_user last(_time) as next_login by ip | table _time user_name ip next_user next_login | where NOT (user_name=next_user) | eval duration=tostring(next_login-_time, "duration")

[swannie]

Thank you both! I wish I could accept both answers because I ended up using bits from both, but ultimately used most of the first one ... 🙂

[ppablo]

Hi @swannie

You can always upvote each answer to give them both some credit for helping you come up with your final solution 🙂 Also, could you please share your final working search for the rest of the Splunk community to learn?