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
Path Finder

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.

1 Solution

stephanefotso
Motivator

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

SGF

View solution in original post

stephanefotso
Motivator

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

SGF

markwymer
Path Finder

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

0 Karma

stephanefotso
Motivator

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"
SGF
0 Karma

markwymer
Path Finder

Many thanks.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...