Detection from Splunk logs for specific events, such as exceeding X number of failed logins over Y time

[dmngaya]

Hi all,

I have this use case below:

Need to create a splunk alert for this scenario: Detections will be created from Splunk logs for specific events like Authentication failed , such as exceeding X number of failed logins over Y time. 

Below search splunk i am using:

index=nprod_database sourcetype=tigergraph:app:auditlog:8542 host=VCAUSC11EUAT* | search userAgent OR "actionName":"login" "timestamp":"2025-01-07T*"| sort -_time

I am not able to write the correct search query to find Authentication failed exceeding, for example 3 times.

Attached screenshot.

Thanks for your help.

Dieudonne.

[gcusello]

Hi @dmngaya ,

at first, as @yuanliu said, please share also samples in text format (using the Insert/Edit Code Sample button).

Then don't use the search command after the main search because your search will be slower: if possible, put all the search terms in the main search.

then, in your search I don't see the login failed condition (e.g. EventCode=4625 in Windows) and you need it in the main search.

Then, I suppose that you need to check the condition for each host in your infrastructure and each account.

Anyway, you have to use the stats command to aggregate results and the where command to filter them, something like this:

(for the login failed condition I use the one from Windows, replace it with your condition)

index=nprod_database sourcetype=tigergraph:app:auditlog:8542 host=VCAUSC11EUAT* userAgent OR "actionName":"login" "timestamp":"2025-01-07T*" EventCode=4625
| stats count BY user host
| where count>3

Adapt it to your real case.

Ciao.

Giuseppe

[dmngaya]

Hi gcusello,

Thanks for your response and suggestions.

1- As yuanliu  said, please see samples in text format (using the Insert/Edit Code Sample button).

2- I put all the search terms in the main search like you said as below:

index=nprod_database sourcetype=tigergraph:app:auditlog:8542 host=VCAUSC11EUAT* clientHost failedAttempts userAgent userName authType message timestamp actionName status "actionName":"login" "message":"Authentication failed" "status":"FAILURE" "timestamp":"2025-01-08T*"

Sample results:

{"endpoint":"/requesttoken","clientHost":"localhost:36976","userAgent":"Apache-HttpClient/5.2.3 (Java/17.0.13)","userName":"TEST6","authType":"LDAP","message":"Generate new token successfully.\nWarning: TEST6 Support cannot restore access to secrets/tokens for security reasons. Please save your secret/token and keep it safe and accessible.","timestamp":"2024-12-30T11:27:02.881-06:00","actionName":"requestToken","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"localhost:53964","failedAttempts":0,"userAgent":"GraphStudio","userName":"TEST1","authType":"LDAP","message":"login succeed","timestamp":"2024-12-30T15:47:15.496-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/schema","clientHost":"localhost:54556","userAgent":"GraphStudio","userName":"TEST1","authType":"LDAP","message":"Successfully got schema for graph aml_risk_graph.","timestamp":"2024-12-30T15:47:33.226-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/authtoken","clientHost":"localhost:54552","userAgent":"GraphStudio","userName":"TEST1","authType":"LDAP","message":"Generate new token successfully.\nWarning: TEST6 Support cannot restore access to secrets/tokens for security reasons. Please save your secret/token and keep it safe and accessible.","timestamp":"2024-12-30T15:47:35.36-06:00","actionName":"requestToken","status":"SUCCESS"},
{"endpoint":"/gsql/userdefinedtokenfunctions","clientHost":"localhost:54556","userAgent":"GraphStudio","userName":"TEST1","authType":"LDAP","message":"getUDFs succeeded","timestamp":"2024-12-30T15:47:38.872-06:00","actionName":"getUDFs","status":"SUCCESS"},
{"endpoint":"/gsql/schema","clientHost":"100.71.65.228","userAgent":"GraphStudio","userName":"TEST1","authType":"LDAP","message":"Successfully got schema for graph aml_risk_graph.","timestamp":"2024-12-30T15:47:39.214-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/file","clientHost":"100.71.65.229","userAgent":"GraphStudio","userName":"TEST1","authType":"LDAP","message":"exportJob succeeded","timestamp":"2024-12-30T15:47:39.292-06:00","actionName":"exportJob","status":"SUCCESS"},
{"endpoint":"/gsql/authtoken","clientHost":"localhost:54552","userAgent":"GraphStudio","userName":"TEST1","authType":"LDAP","message":"Generate new token successfully.\nWarning: TEST6 Support cannot restore access to secrets/tokens for security reasons. Please save your secret/token and keep it safe and accessible.","timestamp":"2024-12-30T15:47:40.63-06:00","actionName":"requestToken","status":"SUCCESS"},
{"endpoint":"/gsql/schema","clientHost":"localhost:54556","userAgent":"GraphStudio","userName":"TEST1","authType":"LDAP","message":"Successfully got schema for graph aml_risk_graph.","timestamp":"2024-12-30T15:47:41.877-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"100.71.65.229","failedAttempts":0,"userAgent":"GraphStudio","userName":"TEST2","authType":"LDAP","message":"login succeed","timestamp":"2024-12-31T10:00:19.455-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"100.71.65.229","failedAttempts":0,"userAgent":"GraphStudio","userName":"TEST2","authType":"LDAP","message":"login succeed","timestamp":"2024-12-31T10:03:22.203-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"localhost:47404","failedAttempts":0,"userAgent":"GraphStudio","userName":"TEST3","authType":"LDAP","message":"login succeed","timestamp":"2024-12-31T10:18:22.9-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"100.71.65.229","failedAttempts":0,"userAgent":"GrpahStudio","userName":"TEST3","authType":"LDAP","message":"Authentication failed!","timestamp":"2024-12-31T10:25:32.26-06:00","actionName":"login","status":"FAILURE"},
{"endpoint":"/requesttoken","clientHost":"localhost:35260","userAgent":"Apache-HttpClient/5.2.3 (Java/17.0.13)","userName":"TEST6","authType":"LDAP","message":"Generate new token successfully.\nWarning: TEST6 Support cannot restore access to secrets/tokens for security reasons. Please save your secret/token and keep it safe and accessible.","timestamp":"2024-12-31T11:00:05.35-06:00","actionName":"requestToken","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"100.71.65.229","failedAttempts":0,"userAgent":"GraphStudio","userName":"TEST3","authType":"LDAP","message":"login succeed","timestamp":"2024-12-31T11:24:31.435-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/schema","clientHost":"localhost:47318","userAgent":"Apache-HttpClient/5.2.3 (Java/17.0.13)","userName":"c089265","authType":"LDAP","message":"Successfully got schema for graph aml_risk_graph.","timestamp":"2024-12-31T21:15:55.995-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/schema","clientHost":"localhost:38336","userAgent":"Apache-HttpClient/5.2.3 (Java/17.0.13)","userName":"c089265","authType":"LDAP","message":"Successfully got schema for graph aml_risk_graph.","timestamp":"2025-01-02T11:36:45.844-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/schema","clientHost":"100.82.128.85","userAgent":"Apache-HttpClient/5.2.3 (Java/17.0.13)","userName":"c089265","authType":"LDAP","message":"Successfully got schema for graph aml_risk_graph.","timestamp":"2025-01-03T03:59:10.235-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"localhost:38012","failedAttempts":0,"userAgent":"GraphStudio","userName":"TEST4","authType":"LDAP","message":"login succeed","timestamp":"2025-01-06T13:47:43.429-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"100.71.65.229","failedAttempts":0,"userAgent":"GrpahStudio","userName":"TEST4","authType":"LDAP","message":"Authentication failed!","timestamp":"2025-01-06T13:48:27.717-06:00","actionName":"login","status":"FAILURE"},
{"endpoint":"/gsql/simpleauth","clientHost":"100.71.65.228","failedAttempts":0,"userAgent":"GrpahStudio","userName":"TEST4","authType":"LDAP","message":"Authentication failed!","timestamp":"2025-01-06T13:48:32.587-06:00","actionName":"login","status":"FAILURE"},
{"endpoint":"/gsql/simpleauth","clientHost":"/127.0.0.1:43520","failedAttempts":0,"userAgent":"GrpahStudio","userName":"TEST4","authType":"LDAP","message":"Authentication failed!","timestamp":"2025-01-06T13:48:36.03-06:00","actionName":"login","status":"FAILURE"},
{"endpoint":"/gsql/simpleauth","clientHost":"localhost:60404","failedAttempts":0,"userAgent":"GraphStudio","userName":"TEST5","authType":"LDAP","message":"login succeed","timestamp":"2025-01-06T19:59:28.295-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"100.71.65.229","failedAttempts":0,"userAgent":"GraphStudio","userName":"TEST5","authType":"LDAP","message":"login succeed","timestamp":"2025-01-06T19:59:40.885-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/login","clientHost":"localhost:53886","clientOSUsername":"TEST4","failedAttempts":0,"userAgent":"GSQL Shell","userName":"TEST4","authType":"LDAP","message":"login succeeded","timestamp":"2025-01-06T20:45:36.492-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/file","clientHost":"10.138.170.165","clientOSUsername":"TEST4","userAgent":"GSQL Shell","userName":"TEST4","authType":"LDAP","message":"showCatalog succeeded","timestamp":"2025-01-06T20:45:37.241-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/login","clientHost":"localhost:39154","clientOSUsername":"TEST4","failedAttempts":0,"userAgent":"GSQL Shell","userName":"TEST4","authType":"LDAP","message":"login succeeded","timestamp":"2025-01-06T20:46:48.666-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/file","clientHost":"10.138.170.165","clientOSUsername":"TEST4","userAgent":"GSQL Shell","userName":"TEST4","authType":"LDAP","message":"showCatalog succeeded","timestamp":"2025-01-06T20:46:49.376-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/login","clientHost":"10.138.170.165","clientOSUsername":"TEST4","failedAttempts":0,"userAgent":"GSQL Shell","userName":"TEST4","authType":"LDAP","message":"login succeeded","timestamp":"2025-01-06T20:47:14.033-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/file","clientHost":"localhost:39154","clientOSUsername":"TEST4","userAgent":"GSQL Shell","userName":"TEST4","authType":"LDAP","message":"Successfully used graph 'aml_risk_graph'.","timestamp":"2025-01-06T20:47:14.863-06:00","actionName":"useGraph","status":"SUCCESS"},
{"endpoint":"/gsql/file","clientHost":"localhost:39154","clientOSUsername":"TEST4","userAgent":"GSQL Shell","userName":"TEST4","authType":"LDAP","message":"Successfully created query 'Del_orphan_edges_for_previous_primary'.","timestamp":"2025-01-06T20:47:17.079-06:00","actionName":"createQuery","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"100.71.65.228","failedAttempts":0,"userAgent":"GraphStudio","userName":"TEST4","authType":"LDAP","message":"login succeed","timestamp":"2025-01-06T20:55:21.895-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/schema","clientHost":"localhost:43048","userAgent":"GraphStudio","userName":"TEST4","authType":"LDAP","message":"Successfully got schema for graph aml_risk_graph.","timestamp":"2025-01-06T20:56:34.057-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/queries","clientHost":"localhost:43048","userAgent":"GraphStudio","userName":"TEST4","authType":"LDAP","message":"showQuery succeeded","timestamp":"2025-01-06T20:56:34.781-06:00","actionName":"showQuery","status":"SUCCESS"},
{"endpoint":"/gsql/file","clientHost":"localhost:39154","clientOSUsername":"TEST4","userAgent":"GSQL Shell","userName":"TEST4","authType":"LDAP","message":"Successfully installed query [Del_orphan_edges_for_previous_primary].","timestamp":"2025-01-06T20:57:36.229-06:00","actionName":"installQuery","status":"SUCCESS"},
{"endpoint":"/gsql/schema","clientHost":"100.71.65.229","userAgent":"GraphStudio","userName":"TEST4","authType":"LDAP","message":"Successfully got schema for graph aml_risk_graph.","timestamp":"2025-01-06T20:57:46.93-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/queries","clientHost":"localhost:60138","userAgent":"GraphStudio","userName":"TEST4","authType":"LDAP","message":"showQuery succeeded","timestamp":"2025-01-06T20:57:47.563-06:00","actionName":"showQuery","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"localhost:48050","failedAttempts":0,"userAgent":"GraphStudio","userName":"sxvedag","authType":"LDAP","message":"login succeed","timestamp":"2025-01-07T08:56:29.476-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/gsql/schema","clientHost":"localhost:48036","userAgent":"GraphStudio","userName":"sxvedag","authType":"LDAP","message":"Successfully got schema for graph aml_risk_graph.","timestamp":"2025-01-07T08:56:38.165-06:00","actionName":"showCatalog","status":"SUCCESS"},
{"endpoint":"/gsql/queries","clientHost":"100.71.65.229","userAgent":"GraphStudio","userName":"sxvedag","authType":"LDAP","message":"showQuery succeeded","timestamp":"2025-01-07T08:56:38.823-06:00","actionName":"showQuery","status":"SUCCESS"},
{"endpoint":"/gsql/simpleauth","clientHost":"100.71.65.228","failedAttempts":0,"userAgent":"GraphStudio","userName":"TEST4","authType":"LDAP","message":"login succeed","timestamp":"2025-01-07T10:16:11.689-06:00","actionName":"login","status":"SUCCESS"},
{"endpoint":"/requesttoken","clientHost":"localhost:45058","userAgent":"Apache-HttpClient/5.2.3 (Java/17.0.13)","userName":"TEST6","authType":"LDAP","message":"Generate new token successfully.\nWarning: TEST6 Support cannot restore access to secrets/tokens for security reasons. Please save your secret/token and keep it safe and accessible.","timestamp":"2025-01-08T09:20:02.381-06:00","actionName":"requestToken","status":"SUCCESS"}
]

But here I don’t know why the filter is giving me information about "status":"SUCCESS" and "message":"login succeed" even my login failed condition is "message":"Authentication failed!"

Maybe my condition in the search query is wrong.

Also I was trying to get the results for the timestamp but it is showing all for other timestamp.

3- I suppose also  to check the condition for each host in our  infrastructure and each account by using my search below:

index=nprod_database sourcetype=tigergraph:app:auditlog:8542 host=VCAUSC11EUAT* clientHost failedAttempts userAgent userName authType message timestamp actionName status "actionName":"login" "message":"Authentication failed" "status":"FAILURE" "timestamp":"2025-01-08T*"

4- I tried  to use the stats command to aggregate results and the where command to filter them, something like this:

index=nprod_database sourcetype=tigergraph:app:auditlog:8542 host=VCAUSC11EUAT* clientHost failedAttempts userAgent userName authType message timestamp actionName status "actionName":"login" "message":"Authentication failed" "status":"FAILURE" "timestamp":"2025-01-08T*" 
| stats count BY actionName  host
| where count>3

Results: No results found.

Thank you for your help.

[yuanliu]

1. Please do not use screenshot to show text data.  Use text box.
2. Show example(s) of complete event, anonymize as needed.  If complete event is too long with lots of repetitive structures, show a complete structure.
3. "events like Authentication failed , such as exceeding X number of failed logins over Y time." Is this condition per user or in total?
4. What counts as 1 number?  One event?  Each entry in an event?  Each user failure in an event?