how can I get the per hour results of the sum of max value per location?

[auaave]

Hi Guys,

I have 10 locations with around 100 spaces each then every 10 mins a new message is sent to update the current empty and available locations. I want to get the maximum total empty and available locations and percentage across all locations per hour.

With the below query, I was able to get the total number of empty, filled, %empty and %filled at the end of the day. how can I get similar results per hour? thank you

| chart eval(max(EMPTYLOCATIONS)) as empty eval(max(LOCATIONS)-max(EMPTYLOCATIONS)) as filled eval(max(EMPTYLOCATIONS)/max(LOCATIONS)*100) as P_EMPTY eval((max(LOCATIONS)-max(EMPTYLOCATIONS))/max(LOCATIONS)*100) as P_OCCUPIED by location
| appendpipe 
    [| stats sum(filled) as filled sum(empty) as empty avg(P_EMPTY) as P_EMPTY avg(P_OCCUPIED) as P_OCCUPIED
    | eval location="all_locations"]

[HiroshiSatoh]

For example, is this like?

 | bin span=1h _time
 | stats eval(max(EMPTYLOCATIONS)) as empty 
              eval(max(LOCATIONS)-max(EMPTYLOCATIONS)) as filled 
              eval(max(EMPTYLOCATIONS)/max(LOCATIONS)*100) as P_EMPTY
              eval((max(LOCATIONS)-max(EMPTYLOCATIONS))/max(LOCATIONS)*100) as P_OCCUPIED 
              by location,_time
 | appendpipe 
     [| stats sum(filled) as filled sum(empty) as empty avg(P_EMPTY) as P_EMPTY avg(P_OCCUPIED) as P_OCCUPIED
          by _time
      | eval location="all_locations"]
 | sort _time

[auaave]

@HiroshiSatoh, thanks for your reply. I need to get the sum /avg of the 10 locations per hour.

When I use "max(emptylocation" per hour using bin time or timechart, it only returns one max value among the 10 locations. What I need is the sum of the max value per location per hour, does it make sense?

[HiroshiSatoh]

I do not know what it is. It specifies ”by location,_time”, so it will be a separate location.

[auaave]

@HiroshiSatoh, not sure but it's not working. I tried the other way, I was able to get the "empty" column with the sum of max value of empty space per locations per hour... my problem now is, I can't add the column for total filled, %empty and %filled

| bin span=1h _time 
| chart eval(max(EMPTYLOCATIONS)) as empty  _time by aisle limit=0 
| addtotals 
| rename Total as empty_total 
| fields _time empty_total 
| appendcols 
    [ | chart eval(max(LOCATIONS)-(max(EMPTYLOCATIONS)) as totalfilled over _time by aisle |addtotals |rename Total as totalfilled| fields totalfilled]

[HiroshiSatoh]

I do not really understand from the search sentence.
Can I present samples of input and output images?