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
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
Hi Nabeel652,
Thanks for your responses, but unfortunately, your answer doesn't return the wanted result.
BR,
Yossi
Try
| stats max(UTILIZATION) as UTILIZATION, max(date_hour) AS HOUR by groupA,groupB
Let us know how that works!
Happy Splunking,
Rich
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
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).
Rich,
Thanks, the last statement was the one I was looking for.
BR,
Yossi
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