Value list

[montydo]

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.

[DMohn · Accepted solution]

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

[montydo]

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

[DMohn]

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?