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

[selinakvle]

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.

[maciep · Accepted solution]

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]

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?

[maciep]

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.

[selinakvle]

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.

[somesoni2]

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]

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

[selinakvle]

I will try this when my instance comes back up. Thanks!