Calculating average mean time to remediate across multiple timespans (30d, 90d, 365d)

[DATT]

My org is pulling in vuln data using the Qualys TA and I am trying to put together a handful of searches and dashboards to see metrics quickly.  I'm using the following currently over the last 30 days:

index=qualys sourcetype=qualys:hostDetection SEVERITY=5 STATUS="FIXED"
| dedup HOST_ID, QID
| eval MTTR = ceiling(((strptime(LAST_FIXED_DATETIME, "%FT%H:%M:%SZ") - strptime(FIRST_FOUND_DATETIME, "%FT%H:%M:%SZ")) / 86400)) 
```| bucket span=1d _time```
| timechart span=1d avg(MTTR) as AVG_MTTR_PER_DAY
| streamstats window=7 avg(AVG_MTTR_PER_DAY) as 7_DAY_AVG

This gets me close, but I believe this is giving the average of averages, not the overall average. Using the month of May, I wouldn't have a calculated value until May 8th, which would use the data from May 1-7.  May 9th would be from May 2-8, etc. 

Any help on how to calculate the overall average?

[ITWhisperer]

Calculate the overall average before the timechart and preserve the value with values aggregate function

index=qualys sourcetype=qualys:hostDetection SEVERITY=5 STATUS="FIXED"
| dedup HOST_ID, QID
| eval MTTR = ceiling(((strptime(LAST_FIXED_DATETIME, "%FT%H:%M:%SZ") - strptime(FIRST_FOUND_DATETIME, "%FT%H:%M:%SZ")) / 86400)) 
```| bucket span=1d _time```
| eventstats avg(MTTR) as OVERALL_AVG
| timechart span=1d avg(MTTR) as AVG_MTTR_PER_DAY values(OVERALL_AVG) as OVERALL_AVG
| streamstats window=7 avg(AVG_MTTR_PER_DAY) as 7_DAY_AVG

[bowesmana]

What is the output - do you want just 3 numbers, 30, 90 and 1y average mttr values or are you looking for a timechart which shows 3 lines with the 30 and 90 day rolling averages?

Averages are easy to calculate over multiple time windows as you can just collect counts and totals, so here's an example of doing the 30 day average and 90 day rolling average

| makeresults count=730
| streamstats c
| eval _time=now() - (86400 * (floor(c/2)))
| eval mttr=random() % 100
| bin _time span=30d
| stats count sum(mttr) as sum_mttr avg(mttr) as mttr_avg_30 by _time
| streamstats window=3 sum(count) as count_90 sum(sum_mttr) as sum_90
| eval rolling_avg_90 = sum_90 / count_90
| eventstats sum(sum_mttr) as total_mttr sum(count) as total_count
| eval annual_avg = total_mttr / total_count
| fields - count_90 sum_90 count sum_mttr total_*

this example generates 2 events per day over a year and takes the 30 day average as well as the count and sum of mttr, so it then uses streamstats to calculate the 90 day rolling average and then finally eventstats to calculate the annual average.