Hi Everyone, I'm sure there are similar queries out there and I have searched however I am still struggling to find a simple answer to this problem.
My query currently is structured like this:
index=hosts "Failed password for " NOT "Failed password for invalid"
| rex "Failed\spassword\sfor\s(?<username>.*)\sfrom\s(?<source_ip>\d+.\d+.\d+.\d+)\sport\s(?<port>\d+)\s(?<protocol>.*)"
| stats count as "Failed Attempts" by source_ip,host,username,
| sort - "Failed Attempts"
| head 20
| table source_ip,host,"Failed Attempts"
And returns data that looks like the following:
source_ip host Failed Attempts
IP.IP.IP.IP hostname1@domain.com 3
IP.IP.IP.IP hostname2@domain.com 2
IP.IP.IP.IP hostname3@domain.com #Count
And what I'd like to achieve is a multi-value list associated with each host as to what username was used
source_ip host Failed Attempts Username
IP.IP.IP.IP hostname@domain.com 3 root
admin
puppet
IP.IP.IP.IP hostname@domain.com 2 root
username1
IP.IP.IP.IP hostname@domain.com #Count #ListOfNames
Any assistance would be greatly appreciated, I've tried transactions, values, and even eval statements and can't find how to display this information cleanly.
This should do it...
index=hosts "Failed password for " NOT "Failed password for invalid"
| rex "Failed\spassword\sfor\s(?<username>.*)\sfrom\s(?<source_ip>\d+.\d+.\d+.\d+)\sport\s(?<port>\d+)\s(?<protocol>.*)"
| stats count as "Failed Attempts", values(username) as Username by source_ip,host
| sort - "Failed Attempts"
| head 20
| table source_ip,host,"Failed Attempts", Username
This should do it...
index=hosts "Failed password for " NOT "Failed password for invalid"
| rex "Failed\spassword\sfor\s(?<username>.*)\sfrom\s(?<source_ip>\d+.\d+.\d+.\d+)\sport\s(?<port>\d+)\s(?<protocol>.*)"
| stats count as "Failed Attempts", values(username) as Username by source_ip,host
| sort - "Failed Attempts"
| head 20
| table source_ip,host,"Failed Attempts", Username
I can't believe it was that simple, I don't know how I missed it.
As a slight extension to this answer would it be possible to have a multi-value list for the IP's as well?
That way I'll have what I've described above, as at the moment I get duplicated hostnames because the IP fields are all different.
It would be nice to have all the IP's grouped in a multi-value style BY hostname, and then all usernames that were attempted (also as a multi-value list as you've achieved in your answer)
Many thanks though
Sure thing ...
If you replace line 3 with | stats count as "Failed Attempts", values(username) as Username, values(source_ip) as source_ip by host
you will have your desired result.
Could you please mark the answer as accepted and close the question if this helped you?