Splunk Search

How to display a total count of results from an IP address instead of listing each event related to that IP?

selinakvle
Explorer

Hi,

I use Splunk at work and I've just downloaded Splunk Light to my personal server to test and learn. I've recently realized that there have been attempts to log in to my personal server via SSH as root. I've already added the authentication logs to Splunk Light but I'm having issues making the data usable.

My search:

source="/var/log/auth.log" host="samplehost" sourcetype="authentication" user!=null | table _time, rhost, user

this search returns results like

Please see attached image.

How do I make it where the duplicate or same rhost shows up only once and their count increases? For example, if the 116. address hits my server 10 times, I'd like to have the IP show only once and a field for count that shows the count of 10.

Thanks in advance.

0 Karma
1 Solution

maciep
Champion

I think you're looking for the stats command

For example, this would give the number of events for each rhost

source="/var/log/auth.log" host="samplehost" sourcetype="authentication" user!=null |stats count by rhost

Or maybe get the count but also a list of the users that show up for each host.

source="/var/log/auth.log" host="samplehost" sourcetype="authentication" user!=null |stats count values(user) as users by rhost

Or possibly, you want to see the latest event for each user from that ip

source="/var/log/auth.log" host="samplehost" sourcetype="authentication" user!=null |stats latest(_time) as last by rhost user | convert ctime(_time)

View solution in original post

maciep
Champion

I think you're looking for the stats command

For example, this would give the number of events for each rhost

source="/var/log/auth.log" host="samplehost" sourcetype="authentication" user!=null |stats count by rhost

Or maybe get the count but also a list of the users that show up for each host.

source="/var/log/auth.log" host="samplehost" sourcetype="authentication" user!=null |stats count values(user) as users by rhost

Or possibly, you want to see the latest event for each user from that ip

source="/var/log/auth.log" host="samplehost" sourcetype="authentication" user!=null |stats latest(_time) as last by rhost user | convert ctime(_time)

selinakvle
Explorer

Thanks!

I was able to achieve what I was looking for with the first solution and I will make use of the other varients. Question, how do I add time back in to the first answer you gave?

0 Karma

maciep
Champion

Glad it worked for you!

I guess I'd have to understand better how you want to see the timestamp again. Do you still want to see the table view like you posted, but just with another field for the count? If so, I think the eventstats command might be better. It does the same agg calculation as stats, but keeps all of the original events too.

source="/var/log/auth.log" host="samplehost" sourcetype="authentication" user!=null |eventstats count by rhost | table _time rhost user count

Or do you want to see a list of timestamps for each remote device?

source="/var/log/auth.log" host="samplehost" sourcetype="authentication" user!=null |stats count list(_time) as timestamps by rhost

Other than those two examples, not sure how else to add the timestamp back in over the aggregrate view.

0 Karma

selinakvle
Explorer

My instance is down right now...I will check back soon and try both methods..thank you both. I promise to come back with my results soon.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Or get the count and last attempt time for each rhost and user combination like this

 source="/var/log/auth.log" host="samplehost" sourcetype="authentication" user!=null |stats count as NoOfAttempts latest(_time) as LastAttemptedOn by rhost user | convert ctime(LastAttemptedOn)

selinakvle
Explorer

Thanks!!! this worked great as well. I want to thank you so much for providing an answer as well!

0 Karma

selinakvle
Explorer

I will try this when my instance comes back up. 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 ...