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.
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
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
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
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"
Many thanks.