topic How to get the top 1 data per host? in Getting Data In

How to get the top 1 data per host?

anirban_nag — Thu, 28 Jun 2018 13:34:27 GMT

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.

Re: How to get the top 1 data per host?

harishalipaka — Thu, 28 Jun 2018 13:49:32 GMT

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

Re: How to get the top 1 data per host?

renjith_nair — Thu, 28 Jun 2018 13:53:13 GMT

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

Re: How to get the top 1 data per host?

anirban_nag — Fri, 29 Jun 2018 15:28:13 GMT

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

Re: How to get the top 1 data per host?

woodcock — Mon, 16 Jul 2018 04:06:11 GMT

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