How to generate a search to only count the first occurrence of an event and ignore any additional event for 2 minutes?

[snix]

I am trying to get a count of successful logins into our web site. The issue is depending on if the user has registered the computer or not, our authentication system may display one or two identical events stating successful.

You only see one event if the computer they are logging in from is registered, as they only put in a username and password. But if the computer is not registered you will see two successful events. One for putting in a user name and password and one for answering the challenge questions correctly. Since there is not a unique final even stating the user passed all authentication mechanisms I need a way to just ignore the second success and I was thinking of just setting saying count the first event you see of UserName+Success and if there is another one within two minuets of the first ignore it.

I know this is not the most accurate way to get a total logins for the day count but it is better than nothing. Just need help with telling the count to ignore any identical additional event within two minutes.

[woodcock · Accepted solution]

Try this:

Your base search here | reverse | streamstats current=f last(_time) AS prev_time BY user host | reverse | where prev_time<=120

[snix]

Sorry for the late response, woodcock that look to do the trick and thanks DalJeanis for the correction!

Just a note in case there is a better way to handle this, I did notice one of the fields did act as a very basic session ID that is localized to specific transactions and not overall to the user itself but more down to the level of a specific task as shown here:

4/17/17 03:45:10 - [SessionID 2] [UserNameHere] [Authenticate Validated]
4/17/17 03:45:10 - [SessionID 2] [UserNameHere] [Junk Event Here]
4/17/17 03:45:10 - [SessionID 2] [UserNameHere] [Junk Event Here]
4/17/17 03:45:10 - [SessionID 2] [UserNameHere] [Authentication Type Question]
4/17/17 03:45:10 - [SessionID 2] [UserNameHere] [Junk Event Here]
4/17/17 03:45:05 - [SessionID 2] [UserNameHere] [Challenge Request Sent]

4/17/17 03:45:05 - [SessionID 1] [UserNameHere] [Authenticate Validated]
4/17/17 03:45:05 - [SessionID 1] [UserNameHere] [Junk Event Here]
4/17/17 03:45:05 - [SessionID 1] [UserNameHere] [Junk Event Here]
4/17/17 03:45:05 - [SessionID 1] [UserNameHere] [Authentication Type Password]
4/17/17 03:45:05 - [SessionID 1] [UserNameHere] [Junk Event Here]
4/17/17 03:45:00 - [SessionID 1] [UserNameHere] [Challenge Request Sent]

Also session ID is not very unique and probably ranges between 1-20 at any given point and you will see multiple of the same id show up given the limited range of IDs but they are different between the password events and the question events. Since they don't bridge the two together I feel I am still in the same boat but maybe someone can think of a way to use this to make it more accurate.

[woodcock]

In that case, just do this:

Your base search here | dedup SessionID UserName

[snix]

The issue is I misspoke the SessionID is more like an even ID so ID itself is not the same between putting in your password and putting in the additional challenge questions as each one is counted as a separate event. So if one user logs in and has registered their PC to our site then they skip the challenge question section and only get a single "Validated" event logged but if they are new to the site or never register their computer to the site then they will get two "Validated" events each with a different Event ID + plus the same user name so it would count as two logins instead of one and I am back in the same boat I was in before you sent me your query.

I don't see how the event ID will help in this situation but figured I would toss it out there to make sure I didn't miss anything. The query you gave me gets me close to the number I need with only a very small fudge factor, I was just trying to see if I can get an exact count but its not the end of the world.

[DalJeanis]

I assume you mean something like | where _time - prev_time <= 120.

,,, verifying sign... latest first, first reverse puts the latest second, so the difference between current and prior is a positive number...

[woodcock]

Arg, good catch; quite so!

[lguinn2]

Is there anything that identifies the second event as being an answer to the challenge question?

[snix]

there is but it is a separate event the follows the first successful event , so it looks like this:

4/17/17 03:45:10 - [Info] [EventIDHere] [AuthenticateUser] [UserNameHere] validated
4/17/17 03:45:10 - [Info] [EventIDHere] [Junk Event] [UserNameHere]
4/17/17 03:45:10 - [Info] [EventIDHere] [Junk Event] [UserNameHere]
4/17/17 03:45:10 - [Debug] [EventIDHere] [Authenticate] [UserNameHere]
4/17/17 03:45:10 - [Info] [EventIDHere] [Authentication Type Challenge Questions] [UserNameHere]
4/17/17 03:45:05 - [Info] [EventIDHere] [UserNameHere] [Challenge Request Sent]

4/17/17 03:45:05 - [Info] [EventIDHere] [AuthenticateUser] [UserNameHere] validated
4/17/17 03:45:05 - [Info] [EventIDHere] [Junk Event] [UserNameHere]
4/17/17 03:45:05 - [Info] [EventIDHere] [Junk Event] [UserNameHere]
4/17/17 03:45:05 - [Debug] [EventIDHere] [Authenticate] [UserNameHere]
4/17/17 03:45:05 - [Info] [EventIDHere] [Authentication Type Password] [UserNameHere]
4/17/17 03:45:00 - [Info] [EventIDHere] [UserNameHere] [Challenge Request Sent]

This is close to how it is laid out where it shows two groups of events one group being related to the password authentication and one being related to the questions authentication and there is no session ID or specific event ID that allows you to tell the two logins apart. The only way you know the event are related is they all share the same username and are within a few seconds of each other most of the time.

[snix]

I had to update the example logs as it didn't reflect the second validation event group to show they were challenge questions and not password and username.