topic Re: comparing min, max and avg of a field by host and application in Splunk Search

comparing min, max and avg of a field by host and application

guru865 — Mon, 02 Oct 2017 13:29:59 GMT

Hi All,

I have been working on a search query but couldn't able to get desired results.

I'm looking for a search which will give me min(field1), max(field1) and avg(field1) of field1 per host per applicationID

My results should have applicationID in the row, and the columns should have hosts and min, max and avg of each host

Results

                                               Host1                                                             Host2                                             Host3
                        min(field1) max(field1) avg(field1)                min(field1) max(field1) avg(field1)        min(field1) max(field1) avg(field1)

appId1

appId2

appId3

Any help on this is greatly appreciated

Thanks

Re: comparing min, max and avg of a field by host and application

Sukisen1981 — Mon, 02 Oct 2017 13:38:13 GMT

what happens if you try -
|stats min(field1), max(field1) ,avg(field1) by host , applicationID
OR
|stats min(field1), max(field1) ,avg(field1) by host , applicationID
???

Re: comparing min, max and avg of a field by host and application

Sukisen1981 — Mon, 02 Oct 2017 13:39:43 GMT

sorry OR
|stats min(field1), max(field1) ,avg(field1) by applicationID,host

Re: comparing min, max and avg of a field by host and application

guru865 — Mon, 02 Oct 2017 13:51:28 GMT

Thanks for your response.

This doesn't give me the results in row vs column. I'm looking for applicationId in rows and host values in cloumn. Each host should have min, max and avg values.
host1 host2
appID1 min, max, avg min,max,avg

Re: comparing min, max and avg of a field by host and application

niketn — Mon, 02 Oct 2017 13:58:13 GMT

@guru865, How many hosts can you have?

You can created three panels one for each host:

<yourbasesearch> host=host1
|stats min(field1), max(field1) ,avg(field1) by applicationID

<yourbasesearch> host=host2
|stats min(field1), max(field1) ,avg(field1) by applicationID

<yourbasesearch> host=host3
|stats min(field1), max(field1) ,avg(field1) by applicationID

If possible please add a screenshot or mock table of the output you need.

Re: comparing min, max and avg of a field by host and application

cmerriman — Mon, 02 Oct 2017 14:06:41 GMT

if you did

   ...|chart min(field1) as min max(field1) as max avg(field1) as avg by applicationID host

does that get you what you need? each column should be named something like min: host1; max: host1; etc. it wouldn't be multi-tiered column headers.

Re: comparing min, max and avg of a field by host and application

guru865 — Mon, 02 Oct 2017 14:17:52 GMT

Thanks Nilay, but there are 36 hosts which i'm looking for

I've been trying to add mock table, but somehow when I post it, it looks all messed up. Let me try again

Host1           Host2       
Min MaxAvg   MinMax Avg

AppId1

AppId2

AppId3

Re: comparing min, max and avg of a field by host and application

guru865 — Mon, 02 Oct 2017 14:22:01 GMT

Thanks for your response.

I tried ......| chart min(field1) as min, max(field1) as max , avg(field1) as avg over appId by host.

This gives me the result but rather than having host name for each min, max and avg value, I want that listed only once in the column and should display the min,max and avg of that server under sub comlumns

Re: comparing min, max and avg of a field by host and application

cmerriman — Mon, 02 Oct 2017 14:40:40 GMT

there is no way to have "sub-columns" in Splunk like there is in Excel. This is, in my understanding, the closest way to do it.

Re: comparing min, max and avg of a field by host and application

guru865 — Mon, 02 Oct 2017 15:08:07 GMT

Thank you. But the search query ...|chart min(field1) as min max(field1) as max avg(field1) as avg by applicationID host is not giving the total results. there are more than 20 hosts but I see only 10 of them in the results and others are complied with the name min(field1):OTHER max(field1):OTHER and avg(field1):OTHER.

Is there any way I can see all the values of each individual host

Re: comparing min, max and avg of a field by host and application

guru865 — Mon, 02 Oct 2017 15:10:53 GMT

I got that by adding limit=0 at the end of the search query. Thankyou

Re: comparing min, max and avg of a field by host and application

andre_tucker — Tue, 03 Oct 2017 11:14:47 GMT

Try this...

<base search>
| stats min(Field1) as min_field1, max(Field1) as max_field1, avg(Field1) as avg_field1 by appID, Host
| eval min_{Host} = min_field1
| eval max_{Host} = max_field1
| eval avg_{Host} = avg_field1
| fields - Host, min_field1, max_field1, avg_field1
| stats values(*) as * by appID

The curly braces in the eval will create the sub-columns.