Splunk Search

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

guru865
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

andre_tucker
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

cmerriman
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

guru865
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

cmerriman
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

guru865
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

guru865
Path Finder

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

0 Karma

Sukisen1981
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

Sukisen1981
Champion

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

0 Karma

guru865
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

niketnilay
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

guru865
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
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!