Splunk Search

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

Path Finder

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

0 Karma

Path Finder

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.

0 Karma

Super Champion

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.

0 Karma

Path Finder

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

0 Karma

Super Champion

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.

0 Karma

Path Finder

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

0 Karma

Path Finder

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

0 Karma

Champion

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

0 Karma

Champion

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

0 Karma

Path Finder

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

0 Karma

Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Path Finder

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!