topic Re: How to search the count of successful and failed logins, the ratio by IP, and the usernames successfully logged in from those IP addresses? in Splunk Search

How to search the count of successful and failed logins, the ratio by IP, and the usernames successfully logged in from those IP addresses?

markwymer — Thu, 04 Jun 2015 05:55:34 GMT

Hi all,

'fraid I'm still a newbie, so I am probably trying to do too much or the impossible but I'll try and explain what I am trying to achieve.

One of the logs that we are feeding into Splunk contains (amongst the millions of events) data that provides info for logon status, IP address and username and real name e.g.
(cut down example)
Logon for 192.168.0.10 username abc@mail.com realname Jeff
Logon error 192.168.0.10 username mylogonname realname Bert
etc

I've created field extractions called Logon_Status, Logon_Source_IP, Logon_Username and Logon_Realname- so far so good.

I need, now, to create a report based on the logon success ratio to logon failure by IP address. Then using the IP information from that, search for any usernames that were successfully logged on to from those IP addresses. e.g.
192.168.0.10 abc@mail.com Jeff

To get the logon ratio, I used:-

source="splunk_testdata.log" host="test" index="testdata"  | stats count(eval(Logon_Status="for")) as Successful, count(eval(Logon_Status="error")) as Failed by Logon_Source_IP | where ((Successful/Failed) * 100 > 0.000001) AND ((Successful/Failed) * 100 < 10)

This gives me the correct list of IP addresses and, having recently read about subsearches, I thought that I could use that to get the rest of the required results for those IP addresses:-

source="splunk_testdata.log" host="test" index="testdata" Logon_Status="for" [ search source="splunk_testdata.log" host="test" index="testdata" logon | stats count(eval(Logon_Status="for")) as Successful, count(eval(Logon_Status="error")) as Failed by Logon_Source_IP | where ((Successful/Failed) * 100 > 0.000001) AND ((Successful/Failed) * 100 < 10) | fields Logon_Source_IP ]  | table Logon_Source_IP, Logon_Username, Logon_Realname

In the table, I can see the correct data for Logon_Source_IP, Logon_Username and Logon_Realname.

What I would, ideally, like to do as add the 'Successful' and 'Failed' counts, calculated from the subsearch. Is this at all possible or would I have to another 'stats count' somewhere in my search?

Am I approaching this problem from the wrong direction by using subsearches to isolate the IP addresses?

The table that I am trying to create is.....

IP Address    Account       Real Name   Total Successful For IP Addr   Total Failed For IP Addr
192.168.0.10  abc@mail.com  Jeff                      1                           22

(hope the formatting works!)

Any and all help gratefully received,
Mark.

Re: How to search the count of successful and failed logins, the ratio by IP, and the usernames successfully logged in from those IP addresses?

stephanefotso — Thu, 04 Jun 2015 09:59:40 GMT

Hello! I think you can build your table whithout using a subsearch. Try this

 source="splunk_testdata.log" host="test" index="testdata"  | eventstats count(eval(Logon_Status="for")) as Successful by Logon_Source_IP| eventstats  count(eval(Logon_Status="error")) as Failed by Logon_Source_IP | where ((Successful/Failed) * 100 > 0.000001) AND ((Successful/Failed) * 100 < 10) |stats  values(Logon_Username) as Account values(Logon_Realname) as "Real Name"  values(Successful) as "Total Successful For IP Addr"  values(Failed) as "Total Failed For IP Addr" by Logon_Source_IP|rename Logon_Source_IP as "IP Addres"

Thanks

Re: How to search the count of successful and failed logins, the ratio by IP, and the usernames successfully logged in from those IP addresses?

markwymer — Thu, 04 Jun 2015 14:00:24 GMT

Hi Stephan,

Thanks for sparing the time to post your solution - it looks a lot neater than mine!

Without wishing to seem ungrateful, the search returns all of the Account names whether they successfully logged in to or not. I only, really, need to see the usernames for the accounts that where successfully logged into in the final report.

But, again, thank you for pointing me in the right direction, I'll

Re: How to search the count of successful and failed logins, the ratio by IP, and the usernames successfully logged in from those IP addresses?

stephanefotso — Thu, 04 Jun 2015 19:19:25 GMT

In that case, just remove Failed values on your table. Here you go!

source="splunk_testdata.log" host="test" index="testdata"  | eventstats count(eval(Logon_Status="for")) as Successful by Logon_Source_IP| eventstats  count(eval(Logon_Status="error")) as Failed by Logon_Source_IP | where ((Successful/Failed) * 100 > 0.000001) AND ((Successful/Failed) * 100 < 10) |stats  values(Logon_Username) as Account values(Logon_Realname) as "Real Name"  values(Successful) as "Total Successful For IP Addr"  by Logon_Source_IP|rename Logon_Source_IP as "IP Addres"

Re: How to search the count of successful and failed logins, the ratio by IP, and the usernames successfully logged in from those IP addresses?

markwymer — Fri, 05 Jun 2015 07:26:37 GMT

Many thanks.