Event following another event to find if login is successful

[PiotrAp]

Hi,

I’m looking for query which helps me to find if login is successful or not. Unfortunately, there is no direct log which would show this, so I need to use following logic:

1. If there is EventID 1000, check if there is following EventID 1001 with the same filed called Username in time range of 1s
2. If EventID with above conditions exist – Status=SUCCESSS
3. If EventID with above conditions doesn’t exist – Status=FAILURE
4. Disaply table with following fields with match both events:

• _Time of event 1000
• Computer from event 1000
• Status
• Resource from event 1001

Is it possible to get this in Splunk?

[malix_la_harpe]

Hello @PiotrAp ,

You can try something like that :

| makeresults
| eval event_id=1000, username="test", Computer="xx1", _time=strptime("2025-06-30 16:26:27.01", "%Y-%m-%d %H:%M:%S.%N"), resource="example1"
| append 
    [| makeresults
| eval event_id=1000, username="test", Computer="xx2", _time=strptime("2025-06-30 16:26:27.02", "%Y-%m-%d %H:%M:%S.%N"), resource="example2"]
| append 
    [| makeresults
| eval event_id=1001, username="test", _time=strptime("2025-06-30 16:26:27.03", "%Y-%m-%d %H:%M:%S.%N"), resource="example3"]
| append 
    [| makeresults
| eval event_id=1000, username="truc", Computer="yyy", _time=strptime("2025-06-30 16:26:29", "%Y-%m-%d %H:%M:%S"), resource="example2"]
| append 
    [| makeresults
| eval event_id=1001, username="truc", Computer="yyy", _time=strptime("2025-06-30 16:26:32", "%Y-%m-%d %H:%M:%S"), resource="example3"]
| sort _time
| streamstats time_window=1s last(event_id) AS current_event_id, last(eval(if(event_id=1000,event_id,null()))) AS previous_event_id, last(eval(if(event_id=1000,_time,null()))) AS previous_time, last(eval(if(event_id=1000,Computer,null()))) as previous_computer, last(resource) AS current_resource by username
| eval status = if(current_event_id=1001 and previous_event_id=1000,"SUCCESS","FAILURE")

(The makeresults lines are here to generate some data to test the query)

In the results, you can see that the "success" status has the time & the computer of the previous event 1000 in the "previous_time" and "previous_computer" fields, and the resource of the event 1001 in the current_resource field.  (I handled the case you have multiple 1000 event before the 1001, we want to keep only the fields of the last 1000 event)

The user "truc" doesn't have a success event because the 2 events aren't in a 1s time window.

If you run this query you will see the results like :

Computer	_time	current_event_id	current_resource	event_id	previous_computer	previous_event_id	previous_time	resource	status	username
xx1	2025-06-30 16:26:27.010	1000	example1	1000	xx1	1000	1751293587.010000	example1	FAILURE	test
xx2	2025-06-30 16:26:27.020	1000	example2	1000	xx2	1000	1751293587.020000	example2	FAILURE	test
	2025-06-30 16:26:27.030	1001	example3	1001	xx2	1000	1751293587.020000	example3	SUCCESS	test
yyy	2025-06-30 16:26:29.000	1000	example2	1000	yyy	1000	1751293589.000000	example2	FAILURE	truc
yyy	2025-06-30 16:26:32.000	1001	example3	1001				example3	FAILURE	truc

Does that answer your question? 🙂

[PiotrAp]

Hi @malix_la_harpe 

Many thanks for this comprehensive answer.

I've been testing the query and it gives promising results, however I have one issue and I hope you will be able to help me.

In the results table there shouldn't be example2 row with FAILURE result as this is a begin of login process successfully completed in example3 row. In other words, the row example2 should be removed from the table. I've tried to adjust the query but unfortunately wasn't able and I hope you will be able to help me. I hope I explained the problem clearly - if not, please let me know.

[malix_la_harpe]

Hello @PiotrAp ,

In the query you can see the "SUCCESS" status on the second step of the authentication, so on the event 1001. The status of the event 1000 corresponding to this auth is still FAILURE because the second step didn't already happen.
If you add a "| search event_id=1001" at the end of the search, does that solve your problem? You will have only the success event for user "test", but you will not have all the 1000 events that don't have a 1001 after.
What do you want to keep exactly ? 🙂

[PiotrAp]

Hi @malix_la_harpe 

Again, many thanks for your help.

Unfortunately adding  a "| search event_id=1001" won't resolve the issue as it will show only successful logins. I'm looking for query which will show also failure logins which are determined as follow:

1. If there is evenr_id 1000 and there is no following event_id 1001 in 1s for the same username - the login is FAILURE

2. If there is evenr_id 1000 and there is following event_id 1001 in 1s for the same username - the login is SUCESS

And the query doesn't take into account condition in second point as  it displays FAILURE even there is following event_if 1001 in 1s for the same user name. In other words the FAILURE part doesn't work.

I hope I've explained this clearly.

[malix_la_harpe · Accepted solution]

Hello @PiotrAp 

Maybe you can do something like that?
I added a second streamstats to keep only the results who don't have an associated event (No 1000 event for a 1001, and no 1001 for a 1000), and also remove the closest 1000 event to a successful 1001 :

| makeresults
| eval event_id=1000, username="test", Computer="xx1", _time=strptime("2025-06-30 16:26:27.01", "%Y-%m-%d %H:%M:%S.%N"), resource="example1"
| append 
    [| makeresults
| eval event_id=1000, username="test", Computer="xx2", _time=strptime("2025-06-30 16:26:27.02", "%Y-%m-%d %H:%M:%S.%N"), resource="example2"]
| append 
    [| makeresults
| eval event_id=1001, username="test", _time=strptime("2025-06-30 16:26:27.03", "%Y-%m-%d %H:%M:%S.%N"), resource="example3"]
| append 
    [| makeresults
| eval event_id=1000, username="truc", Computer="yyy", _time=strptime("2025-06-30 16:26:29", "%Y-%m-%d %H:%M:%S"), resource="example2"]
| append 
    [| makeresults
| eval event_id=1001, username="truc", Computer="yyy", _time=strptime("2025-06-30 16:26:32", "%Y-%m-%d %H:%M:%S"), resource="example3"]
| sort _time
| streamstats time_window=1s count as nb last(event_id) AS current_event_id, last(eval(if(event_id=1000,event_id,null()))) AS previous_event_id, last(eval(if(event_id=1000,_time,null()))) AS previous_time, last(eval(if(event_id=1000,Computer,null()))) as previous_computer, last(resource) AS current_resource by username
| eval status = if(current_event_id=1001 and previous_event_id=1000,"SUCCESS","FAILURE")
| reverse
| streamstats time_window=1s max(eval(if(event_id=1000,nb,null()))) as max_nb values(status) as statuses by username
| where mvcount(statuses)=1 or nb!=max_nb
| fields - statuses current_event_id current_resource max_nb nb previous_event_id

The query is not very elegant, but works if I understood well what you want.
Maybe someone will have a prettier solution 🙂

Don't hesitate to tell me if it suits your need

[PiotrAp]

@malix_la_harpe 

Many thanks for all advises. I've modified the query and added dedup - solution and seems to be working well. However what you proposed does the job as well. I really appreciate time which you spend helping me!

| makeresults
| eval event_id=1000, username="test", Computer="xx1", _time=strptime("2025-06-30 16:26:27.01", "%Y-%m-%d %H:%M:%S.%N"), resource="example1"
| append
[| makeresults
| eval event_id=1000, username="test", Computer="xx2", _time=strptime("2025-06-30 16:26:27.02", "%Y-%m-%d %H:%M:%S.%N"), resource="example2"]
| append
[| makeresults
| eval event_id=1001, username="test", _time=strptime("2025-06-30 16:26:27.03", "%Y-%m-%d %H:%M:%S.%N"), resource="example3"]
| append
[| makeresults
| eval event_id=1000, username="truc", Computer="yyy", _time=strptime("2025-06-30 16:26:29", "%Y-%m-%d %H:%M:%S"), resource="example2"]
| append
[| makeresults
| eval event_id=1001, username="truc", Computer="yyy", _time=strptime("2025-06-30 16:26:32", "%Y-%m-%d %H:%M:%S"), resource="example3"]
| sort _time
| streamstats time_window=1s values(_time) as Time values(Computer) as Computer_name values(event_id) AS EventID, last(eval(if(event_id=1000,event_id,null()))) AS previous_event_id, count(eval(event_id)) as EventCount, last(eval(if(event_id=1000,_time,null()))) AS previous_time by username
| dedup previous_time username sortby EventCount desc
| eval status = if(EventCount>1,"SUCCESS","FAILURE")
| table Time Computer_name EventID username resource status
| sort Time

[malix_la_harpe]

You're right, with the dedup it's better
I'm glad we came to a solution together
Happy splunking! 😀