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. 

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!

Splunk Federated Analytics for Amazon Security Lake

Thursday, November 21, 2024  |  11AM PT / 2PM ET Register Now Join our session to see the technical ...

Splunk With AppDynamics - Meet the New IT (And Engineering) Couple

Wednesday, November 20, 2024  |  10AM PT / 1PM ET Register Now Join us in this session to learn all about ...

Building a Self-Service and Scalable Observability Practice

Thursday, November 14, 2024  |  11AM PT / 2PM ET Register Now Join us in this session and learn how Splunk ...