Splunk Search

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

auaave
Communicator

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

HiroshiSatoh
Champion

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

auaave
Communicator

@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?

0 Karma

HiroshiSatoh
Champion

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

0 Karma

auaave
Communicator

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

HiroshiSatoh
Champion

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...