Dashboards & Visualizations

Concatenate failed and successful logins/authentication?

Lye
Path Finder

I would like to detect successful authentication after a brute force attempt. It would be nice to see multiple status code 400s and the 200s all from the same IP. That way, I do not have to do multiple searches for every IP. I used the below query but was unsuccessful. Please help if you can

index=[index name] sourcetype=[sourcetypename] httpmethod=* status code=*
| eventstats count(eval('action'=="success")) AS success, count(eval('action'=="failure")) AS failure BY src_ip
| where total_success>=1 AND total_failure>=15
| stats count by src_ip

In between I even added
|strcat success . failure
but could not get results. Kindly assist. 

Thank you.

Labels (1)
1 Solution

bowesmana
SplunkTrust
SplunkTrust

This query you posted

index=[index name] sourcetype=[sourcetypename] httpmethod=* status code=*
| stats count by src_ip

would not give you a table with httpmethod and status_code as you have not collected either of those fields in the stats command.

If you want the count of 400/200 for each IP then you would add this into the stats

| stats count(eval(status_code=400)) as failed_count count(eval(status_code=200)) as success_count by src_ip

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

@Lye Using eventstats will not be the most performant solution and is not really necessary, instead you should just use 

index=[index name] sourcetype=[sourcetypename] httpmethod=* status code=*
| stats values(*) as * count by src_ip action
| eval {action}=count
| fields - count
| stats values(*) as * by src_ip
| where success>=1 AND failure>=15

 In the above, note that the first values(*) as * is just a mechanism to bring all the other fields along with the stats data - so change that to limit the field collection to what you want to pull through, e.g.

| stats values(_time) as _time, values(httpmethod) as httpmethod values(status_code) as status_code...

The second values(*) as * should remain as it is.

 

0 Karma

Lye
Path Finder

@bowesmana  Thank you for your response. I got some results with the queries you posted. However, it did not yield the result I anticipated. For example,
when I just tried this below:
index=[index name] sourcetype=[sourcetypename] httpmethod=* status code=*
| stats count by src_ip

I got in a tabular form src_ip, httpmethod, status code and the count for each IP. I was hoping to have a search that will yield on each IP the number of 400s, 200s and each count. That way I don't have to do a search differently for each IP in the table looking for their respective counts of 400s and 200s.

Tags (1)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

This query you posted

index=[index name] sourcetype=[sourcetypename] httpmethod=* status code=*
| stats count by src_ip

would not give you a table with httpmethod and status_code as you have not collected either of those fields in the stats command.

If you want the count of 400/200 for each IP then you would add this into the stats

| stats count(eval(status_code=400)) as failed_count count(eval(status_code=200)) as success_count by src_ip

 

Lye
Path Finder

@bowesmana Yes, the query you sent worked. Thank you so much.

0 Karma

inventsekar
SplunkTrust
SplunkTrust

I would like to detect successful authentication after a brute force attempt. It would be nice to see multiple status code 400s and the 200s all from the same IP. That way, I do not have to do multiple searches for every IP.

that's a very good use-case.
hope you checked the other reply SPL query..

let us know how it went.. we will help you in troubleshooting further.. thanks. 

thanks and best regards,
Sekar

PS - If this or any post helped you in any way, pls consider upvoting, thanks for reading !
0 Karma

johnhuang
Motivator

Something like this:

 

index=[index name] sourcetype=[sourcetypename] httpmethod=* status code=*
| eval {action}=1
| eventstats count AS total_ct sum(success) AS success_ct sum(failure) AS failure_ct BY src_ip
| where success_ct>0 AND failure_ct>14

Lye
Path Finder

@johnhuang , Thank you for your response. I tried it, but no result came up. Do you have any other advice you might give?

0 Karma

johnhuang
Motivator

Could you provide the result of:

 

index=[index name] sourcetype=[sourcetypename] httpmethod=* status code=*
| values(action) AS action

0 Karma

Lye
Path Finder

@johnhuang , with this
 index=[index name] sourcetype=[sourcetypename] httpmethod=* status code=*
| values(action) AS action

I got nothing.  Did you mean this below?:

index=[index name] sourcetype=[sourcetypename] httpmethod=* status code=*
| stats values(action) AS action

With the latter, I got a list of the actions in a table.

0 Karma

johnhuang
Motivator

What are the action values?

0 Karma

Lye
Path Finder

@johnhuang failure, delivered, blocked

0 Karma

johnhuang
Motivator

It's missing "success"?

0 Karma

Lye
Path Finder

Yes it is

0 Karma

Lye
Path Finder

@johnhuang Thank you for you help. The Query from @bowesmana worked.

0 Karma
Get Updates on the Splunk Community!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...