Splunk Search

Showing the latest time per single unique value in multiple fields

davidworsnop
Explorer

Hello, my question is linked to the below answer.
https://answers.splunk.com/answers/222406/search-to-group-by-country-city-having-count-sorte.html

I'm trying to produce a table that shows
1. City - IP Address - Last seen - Total attacks per IP Address

So far I have:
* | iplocation src | eval City=if(isnull(City) OR City="", "Unknown City", City) |stats values(src) as "IP Address", latest(_time) as "Last seen", count(src) as "Total Count" by City |convert timeformat="%d/%m/20%y %H:%M:%S" ctime("Last seen")

Problem is that this only shows one lastest(_time) per City, not the latest time each individual IP address was seen id there is more than one IP address per City.

Thanks very much in adavnce

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Have you considered splitting by City IP?

Then you'll get one row per unique City-IP-combination, and thereby one latest time per combination.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

Have you considered splitting by City IP?

Then you'll get one row per unique City-IP-combination, and thereby one latest time per combination.

martin_mueller
SplunkTrust
SplunkTrust

Add this to the end:

... | streamstats window=1 current=f last(City) as last_City | eval City = case(NOT City=last_City, City) | fields - last_City

That'll only keep the first occurrence per City, and drop identical ones afterwards.

davidworsnop
Explorer

Fantastic! Thank you so much. 🐵

0 Karma

davidworsnop
Explorer

Good idea! Thanks very much for the speedy response!

So I'm closer but now I have multiple values of the same City name in my first column and although its sorted alphabetically and therefore the duplicate values are grouped, it would look a bit tidier if the City name only appeared once.

*| iplocation src allfields=true | eval City=if(isnull(City) OR City="", "Unknown City", City) | rename src as "IP Address"| stats latest(_time) as "Last seen", count("IP Address") as "Total Count attacks per City" by City "IP Address" | convert timeformat="%d/%m/20%y %H:%M:%S" ctime("Last seen")

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