Splunk Search

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

Explorer

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

Tags (3)
0 Karma

Builder

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
0 Karma

Explorer

Hi Nabeel652,

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

Yossi

0 Karma

SplunkTrust
SplunkTrust

Try

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

Let us know how that works!

Happy Splunking,
Rich

Explorer

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_1 0.8 23
groupA_1 groupB_2 0.75 23
groupA_2 groupB_1 0.25 23
groupA_2 groupB_2 0.65 23

While i would like to get the date_hour when it correlate to the max(UTILIZATION) field, 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

0 Karma

SplunkTrust
SplunkTrust

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

Explorer

Rich,

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

BR,

Yossi

0 Karma

Community Manager
Community Manager

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

0 Karma