Splunk Search

Event following another event to find if login is successful

PiotrAp
Path Finder

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?

Labels (1)
0 Karma
1 Solution

malix_la_harpe
Explorer

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

View solution in original post

malix_la_harpe
Explorer

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_timecurrent_event_idcurrent_resourceevent_idprevious_computerprevious_event_idprevious_timeresourcestatususername
           
xx12025-06-30 16:26:27.0101000example11000xx110001751293587.010000example1FAILUREtest
xx22025-06-30 16:26:27.0201000example21000xx210001751293587.020000example2FAILUREtest
 2025-06-30 16:26:27.0301001example31001xx210001751293587.020000example3SUCCESStest
yyy2025-06-30 16:26:29.0001000example21000yyy10001751293589.000000example2FAILUREtruc
yyy2025-06-30 16:26:32.0001001example31001   example3FAILUREtruc



Does that answer your question? 🙂

PiotrAp
Path Finder

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.

PiotrAp_1-1751375831875.png

 

 

0 Karma

malix_la_harpe
Explorer

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 ? 🙂

0 Karma

PiotrAp
Path Finder

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.

0 Karma

malix_la_harpe
Explorer

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
Path Finder

@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
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...