Timechart/bin - "flatten" values

[bcarr12]

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!

[DalJeanis]

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