Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- How to correlate the stats sum function with the a...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Highlighted
##
How to correlate the stats sum function with the appropriate date_hour field located in the same row?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

yyossef

Explorer

12-19-2016
12:28 AM

Hi,

I have a problem using max function with stats command.

I am tryng to find the max utilization value and at the some time find when it happen, based on the date_hour field.

The following tables represent the data i have:

```
date_hour groupA groupB FREE TOTAL UTILIZATION
0 groupA_1 groupB_1 10 20 0.5
0 groupA_1 groupB_2 15 20 0.75
0 groupA_2 groupB_1 5 20 0.25
0 groupA_2 groupB_2 12 20 0.6
.
.
.
23 groupA_1 groupB_1 16 20 0.8
23 groupA_1 groupB_2 11 20 0.55
23 groupA_2 groupB_1 4 20 0.2
23 groupA_2 groupB_2 13 20 0.65
```

I am running the following stats command

```
| stats max(UTILIZATION) as UTILIZATION by groupA,groupB
```

The Result is:

```
groupA_1 groupB_1 0.8
groupA_1 groupB_2 0.75
groupA_2 groupB_1 0.25
groupA_2 groupB_2 0.65
```

I would like to attached the appropriate date_time to each row, such as:

```
groupA_1 groupB_1 0.8 23
groupA_1 groupB_2 0.75 0
groupA_2 groupB_1 0.25 0
groupA_2 groupB_2 0.65 23
```

I would appreciate yours help.

BR,

Yossi

Highlighted
##
Re: How to correlate the stats sum function with the appropriate date_hour field located in the same row?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Richfez

SplunkTrust

12-19-2016
05:08 AM

Try

```
| stats max(UTILIZATION) as UTILIZATION, max(date_hour) AS HOUR by groupA,groupB
```

Let us know how that works!

Happy Splunking,

Rich

Highlighted
##
Re: How to correlate the stats sum function with the appropriate date_hour field located in the same row?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

yyossef

Explorer

12-19-2016
05:25 AM

Hi Rich,

It does not work, the max function return the Max date*hour of the input without correnation to the UTILIZATION field, the suggested query return the following:
groupA*1 groupB

groupA

groupA

groupA

While i would like to get the date*hour when it correlate to the max(UTILIZATION) field, such as:
groupA*1 groupB

groupA

groupA

groupA

Highlighted
##
Re: How to correlate the stats sum function with the appropriate date_hour field located in the same row?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Richfez

SplunkTrust

12-20-2016
04:41 AM

OH! I see.

In looking around for precisely the right way to structure an answer, I stumbled across this great post to find the max peak hour of volume by strive.

Specifically, he writes:

```
Some search terms... | bucket _time span=1h | stats count(Model Sold) as Total_Sales by _time, Make, "Model Sold" | eventstats max(Total_Sales) as Max by _time, Make | where Total_Sales = Max | rename Total_Sales as "No of Models Sold" | fields - Max
```

But you don't need all that. From the beginning - the `| bucket ...`

and first `| stats ...`

aren't needed because you are using date*hour (although if I may suggest, you should investigate using exactly what the sample search above is using because date*hour has some problems). The `| eventstats ... | where ...`

is similar to what you need. The last things are just making it pretty.

So try this.

```
... | eventstats max(UTILIZATION) as max_utilization by groupA, groupB | where UTILIZATION=max_utilization
```

I think that's going to get you very close, only needing some tidying up and/or formatting to make it just right for your needs. We *might* need a combination of both stats and eventstats, but try the above and see where that gets you for now.

Happy Splunking,

Rich

If you find strive's answer useful, even if it didn't lead directly to your answer, please consider upvoting it to show him some appreciation! (I for one think it's a beautiful answer, very well done and useful).

Highlighted
##
Re: How to correlate the stats sum function with the appropriate date_hour field located in the same row?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

yyossef

Explorer

12-20-2016
04:55 AM

Rich,

Thanks, the last statement was the one I was looking for.

BR,

Yossi

Highlighted
##
Re: How to correlate the stats sum function with the appropriate date_hour field located in the same row?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

ppablo

Community Manager

12-20-2016
03:30 PM

Hi @yyossef

Glad you found a solution through @rich7177 Please don't forget to resolve the post by clicking Accept directly below his answer. Also, upvote the answer/comments that were especially helpful.

Cheers

Highlighted
##
Re: How to correlate the stats sum function with the appropriate date_hour field located in the same row?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

nabeel652

Builder

12-19-2016
07:04 PM

The format of the output may differ a bit but the information is what you want so

try this:

```
| timechart span=1h max(UTILIZATION) AS UTILIZATION by groupA,groupB |eval hour=strftime(_time,"%H") | table hour groupA groupB
```

Highlighted
##
Re: How to correlate the stats sum function with the appropriate date_hour field located in the same row?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

yyossef

Explorer

12-20-2016
12:43 AM

Hi Nabeel652,

Thanks for your responses, but unfortunately, your answer doesn't return the wanted result.

BR,

Yossi