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

[jturner900]

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.

[pavankumarh]

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 · Accepted solution]

Try this

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

[jturner900]

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

[somesoni2]

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]

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