Splunk Search

Timechart/bin - "flatten" values

bcarr12
Path Finder

What would be the best way to run a week to date search (timechart/bin) that "flattens" the individual days so I can get an average count per minute for the week? I don't care so much about the count per minute per day, but the average count each minute taking the entire week into account.

For instance, if I want to take "timechart span=1m count" and run that week to date, but ignore the dates and only focus on times. The idea would be the have the avg(count) at 8:00, 8:01, 8:02 etc and compare that to the "current" count today.

Ideally I'm looking to run a search for Today, timechart span=1m count - and add avg(count) per minute for the prior week to give an idea for how today compares to historical data.

Thanks!

0 Karma

DalJeanis
Legend

Assuming that you can be relatively certain that count is at least 1 for every minute of the day...

your base search
| eval Day=strftime(_time,"%Y-%m-%d")
| eval Minute=strftime(_time,"%H:%M:%S")
| stats count as minutecount by Minute Day
| eventstats max(Day) as Today
| eval Today=if(Day=Today,minutecount,null())  
| eval Week=if(Day=Today,null(),minutecount)  
| stats avg(*) as * by Minute

Assuming that you CANNOT be relatively certain that count is at least 1 for every minute of the day, here's one way to fill in the missing zeroes...

your base search
| eval Day=strftime(_time,"%Y-%m-%d")
| eval Minute=strftime(_time,"%H:%M:%S")
| stats count as minutecount by Minute Day
| append 
    [| makeresults 
     | eval Time=mvrange(relative_time(now(),"-6d@d"),relative_time(now(),"@d"),60) 
     | mvexpand Time     
     | eval Day=strftime(Time,"%Y-%m-%d") 
     | eval Minute=strftime(Time,"%H:%M:%S")
     | table Day Minute 
     | eval minutecount=0
    ]
| stats sum(minutecount) as minutecount by Minute Day
| eventstats max(Day) as Today
| eval Today=if(Day=Today,minutecount,null())  
| eval Week=if(Day=Today,null(),minutecount)  
| stats avg(*) as * by Minute
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...