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

[guru865]

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

[andre_tucker]

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.

[cmerriman]

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.

[guru865]

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

[cmerriman]

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.

[guru865]

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

[guru865]

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

[Sukisen1981]

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

[Sukisen1981]

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

[guru865]

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

[niketn]

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

[guru865]

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