Splunk Search

Value list

montydo
Explorer

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.

0 Karma
1 Solution

DMohn
Motivator

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

View solution in original post

0 Karma

DMohn
Motivator

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

montydo
Explorer

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

0 Karma

DMohn
Motivator

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?

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 ...