Concatenate failed and successful logins/authentication?

[Lye]

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.

[bowesmana]

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

[Lye]

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

[bowesmana · Accepted solution]

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]

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

[inventsekar]

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. 

[johnhuang]

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]

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

[johnhuang]

Could you provide the result of:

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

[Lye]

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

[johnhuang]

What are the action values?

[Lye]

@johnhuang failure, delivered, blocked

[johnhuang]

It's missing "success"?

[Lye]

Yes it is

[Lye]

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