Splunk Search

How to search the count of host Instances, and get the latest occurrence if there are duplicates?

jturner900
Explorer

In addition, if there is a duplicate host, I'd also like to keep the fields of the latest. Here's an example:

Host            Date        Source          Label
198.162.1.1     1:00:54     198.162.4.5     A
198.162.2.1     3:32:54     198.162.4.5     Q
198.162.1.5     7:33:22     198.162.4.5     B
198.162.2.1     5:50:49     198.162.4.5     R

The output would be

Host            Date        Source         Label    Count 
198.162.1.1     1:00:54     198.162.4.5    A        1
198.162.2.1     5:50:49     198.162.4.5    R        2
198.162.1.5     7:33:22     198.162.4.5    B        1

Since there are two occurrences of the second host, we only want to keep the information of the latest instance.

0 Karma
1 Solution

lguinn2
Legend

Try this

[yoursearchhere]
stats latest(Date) as Date, latest(Source) as Source, latest(Label) as Label, count as Count by Host

View solution in original post

pavankumarh
Path Finder

hi, try this query

index=yourindex |stats values(Date) values(Source) values(Label) count by Host

i tried it on my data and results look like what you asked for.

lguinn2
Legend

Try this

[yoursearchhere]
stats latest(Date) as Date, latest(Source) as Source, latest(Label) as Label, count as Count by Host

jturner900
Explorer

Almost, thanks. However, what happens is if the lastest entry has nothing, it defaults to the latest time that has an entry. For instance,

Host Date Source Label
198.162.2.1 1:00:54 198.162.4.5 A
198.162.2.1 3:32:54 198.162.4.5 Q
198.162.2.1 5:50:49 198.162.4.5

produces

Host Date Source Label
198.162.2.1 5:50:49 198.162.4.5 Q

when it should produce

Host Date Source Label
198.162.2.1 5:50:49 198.162.4.5

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try like this (will show N/A instead of blank.

your base search | fillnull value="N/A" |  stats latest(Date) as Date, latest(Source) as Source, latest(Label) as Label, count as Count by Host 

lguinn2
Legend

or you could fillnull value=" " because the problem is not that the field value is blank, it is that the field value is null...

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