topic Re: Concatenate failed and successful logins/authentication? in Dashboards & Visualizations

Concatenate failed and successful logins/authentication?

Lye — Fri, 30 Sep 2022 23:09:51 GMT

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.

Re: Concatenate failed and successful logins/authentication?

johnhuang — Sat, 01 Oct 2022 00:15:16 GMT

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

Re: Concatenate failed and successful logins/authentication?

inventsekar — Sat, 01 Oct 2022 02:57:26 GMT

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.

Re: Concatenate failed and successful logins/authentication?

bowesmana — Mon, 03 Oct 2022 03:30:37 GMT

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

Re: Concatenate failed and successful logins/authentication?

Lye — Mon, 03 Oct 2022 18:26:41 GMT

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

Re: Concatenate failed and successful logins/authentication?

johnhuang — Mon, 03 Oct 2022 18:29:08 GMT

Could you provide the result of:

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

Re: Concatenate failed and successful logins/authentication?

Lye — Mon, 03 Oct 2022 18:36:57 GMT

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

Re: Concatenate failed and successful logins/authentication?

Lye — Mon, 03 Oct 2022 18:46:24 GMT

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

Re: Concatenate failed and successful logins/authentication?

johnhuang — Mon, 03 Oct 2022 18:55:44 GMT

What are the action values?

Re: Concatenate failed and successful logins/authentication?

Lye — Mon, 03 Oct 2022 19:22:53 GMT

@johnhuang failure, delivered, blocked

Re: Concatenate failed and successful logins/authentication?

johnhuang — Mon, 03 Oct 2022 19:30:59 GMT

It's missing "success"?

Re: Concatenate failed and successful logins/authentication?

Lye — Mon, 03 Oct 2022 19:40:25 GMT

Yes it is

Re: Concatenate failed and successful logins/authentication?

bowesmana — Mon, 03 Oct 2022 23:58:03 GMT

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

Re: Concatenate failed and successful logins/authentication?

Lye — Wed, 05 Oct 2022 15:43:26 GMT

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

Re: Concatenate failed and successful logins/authentication?

Lye — Wed, 05 Oct 2022 15:45:56 GMT

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