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:
Is it possible to get this in Splunk?
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
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? 🙂
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.
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 ? 🙂
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.
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
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
You're right, with the dedup it's better
I'm glad we came to a solution together
Happy splunking! 😀