How to get the top 1 data per host?

[anirban_nag]

I have a log where the mount usage of every host gets logged. So there can be multiple mounts per host. The data can be following -

Host | Mount_Name | Usage
________________________________
host1 | /tmp | 90
host1 | /opt | 92
host2 | /opt | 81
host2 | /tmp | 90

So the result would be -

Host | Mount_Name | Usage
________________________________
host1 | /opt | 92
host2 | /tmp | 90

Which means for every host I need the highest mount usage.

[woodcock]

Like this:

| makeresults 
| eval _raw="Host | Mount_Name | Usage
________________________________
host1 | /tmp | 90
host1 | /opt | 92
host2 | /opt | 81
host2 | /tmp | 90" 

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rex max_match=0 "[\r\n](?<raw>[^\r\n]+\d+)" 
| mvexpand raw
| rex max_match=0 field=raw "^(?<Host>\S+)\s+\|\s+(?<Mount_Name>\/\S+)\s+\|\s+(?<Usage>\d+)$"
| dedup Host Mount_Name
| fields Host Mount_Name Usage
| fields - _*
| eventstats max(Usage) AS max_usage BY Mount_Name
| where Usage==max_usage
| fields - max_usage
| dedup Mount_Name

[renjith_nair]

Hi @anirban_nag,,

Try,

if you need mount point also part of the result

index="your index" sourcetype="your sourcetype" | eventstats max(Usage) as max_usage by Host|where Usage=max_usage

If mount point is not needed,

    index="your index" sourcetype="your sourcetype" | stats max(Usage) by Host

[harishalipaka]

add this end of your query -- |sort -Usage |dedup Host

[anirban_nag]

@harishalipaka your comment is most apt for the question. Please submit an answer and I will accept.