Splunk Search

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

DATT
Path Finder

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?

Labels (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

bowesmana
SplunkTrust
SplunkTrust

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.

 

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

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