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!

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...