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"]
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
@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?
I do not know what it is. It specifies ”by location,_time”, so it will be a separate location.
@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]
I do not really understand from the search sentence.
Can I present samples of input and output images?