Splunk Search

removing duplicate with maximum value

RanjiRaje
Explorer

Hi All, Can anyone please help me on this ...

I am framing a SPL query to get list of hosts with their last eventtime.

SPL query:  

| tstats max(_time) as latest where index=indexname by host  | convert ctime(latest)

From this query, I am getting the list as expected, but with one bug. (If I have a host both in lower case & in upper case, I am getting 2 different entries)

Eg:              host                            latest

             HOSTNAME1               09/17/2024 15:27:49

             hostname1                   08/30/2024 15:27:00

             hostname2                   09/15/2024 15:27:49

             HOSTNAME2               09/13/2024 15:27:49

From here, I have to get only one entry for a host along with latest time. (For hostname1, I should get 09/17/2024 15:27:49, similarly for hostname2 I should get 09/15/2024 15:27:49)

I tried adding the command, 

| eval host=upper(host), latest=max(latest) | dedup host

But it is not considering max of "latest", and it just showing the single row for each host with random value of "latest"

Can you please suggest me the better way to achieve this. thanks

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @RanjiRaje ,

please try this:

| tstats max(_time) as latest where index=indexname by host  
| eval host=upper(host)
| stats max(latest) AS latest BY host
| convert ctime(latest)

Ciao.

Giuseppe

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

You generally thought well but you have to cast your hostname to lowercase (or uppercase; doesn't matter as long as it's consistent) _before_ you do your stats.

EDIT: I didn't notice it started with tstats. Of course in this case @gcusello 's solution is the way to go.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @RanjiRaje ,

please try this:

| tstats max(_time) as latest where index=indexname by host  
| eval host=upper(host)
| stats max(latest) AS latest BY host
| convert ctime(latest)

Ciao.

Giuseppe

RanjiRaje
Explorer

thanks sir 🙂 

I was thinking something complex, but you made it very simple.

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Community Content Calendar, September edition

Welcome to another insightful post from our Community Content Calendar! We're thrilled to continue bringing ...

Splunkbase Unveils New App Listing Management Public Preview

Splunkbase Unveils New App Listing Management Public PreviewWe're thrilled to announce the public preview of ...

Leveraging Automated Threat Analysis Across the Splunk Ecosystem

Are you leveraging automation to its fullest potential in your threat detection strategy?Our upcoming Security ...