Monthly Averages from Daily Metrics Problem

[mgroversigital]

I'm working on using performance analytics to capture MTTA, MTTC data in security incident response.  There were several metrics that came OOB for tracking this and other SIR metrics, and they get added to an SI.completed metric view, which is used as an indicator source for several other SIR PA indicators.  We created our own scripted metric definitions to properly track state changes as the SIR goes through lifecycle updates.  The individual metrics are creating fine for each metric definition, and they're getting added into the SI.completed metric view correctly.   We also created our own automated indicators, but they all leverage the same SI.completed metric view source, which collects daily.  

The issue we realized is how those daily PA snapshots are getting rolled up to monthly values in our dashboards.  Due to the issue with how PA aggregates averages, noted in this KB: KB0754102 , our monthly metrics are off because we're averaging the daily values/days of the month instead of the individual incident metrics/number of incidents occurring for that month.

I'm not a PA expert, but it seems that due to the limitation on rolling up data, we'd have to create separate indicator sources if we want to get weekly, monthly, quarterly rollups accurately.  This isn't ideal because there are a number of breakdowns at each level that we're also wanting to collect, and it seems like it would be ideal to limit the number of indicator sources and breakdowns created.  

Can someone with deeper PA knowledge let me know how to best set this up if you were starting from scratch?  Would formula indicators be a better option, or should we stick with using metrics but separate indicator sources?  Ideally we'd like users to be able to filter the data on the dashboard using a date filter, and click into the single score widget to manipulate date numbers and look at individual record lists, but I think doing separate indicators/sources would necessitate separate widgets on the dashboard for weekly, monthly, quarterly data, etc.

[sizzleMcFace]

You are correct in that if you are using a Daily Indicator and then averaging for let's say a month, you are taking the average of averages, which will not be accurate.

Instead, it is best to separate the 2 parts of the original average to 2 indicators.

For example, an MTTR calculation would have 1 COUNT Indicator to count the records on that day, and 1 SUM Indicator to sum the resolution times of those records. Then you would have a formula indicator which divides SUM by COUNT. This gives you the exact same average result on a daily basis.

However, if you now aggregate by month (using "By Month SUM"), the formula would look like "SUM(sum of all sum scores from the month) divided by COUNT (sum of all COUNT scores from the month).

Example:

Day 1:

• inc1 - 1 days to resolve
• Inc2 - 2 days to resolve

Day 2:

• Inc3 - 3 days to resolve
• Inc4 - 4 days to resolve
• Inc5 - 5 days to resolve
• Inc6 - 6 days to resolve

If you use the original solution:

• Day 1 score would be 3/2=1.5
• Day 2 score would be 18/4=4.5
• Visualization would show an average of (1.5+4.5)/2=3 for the month.

With the new solution:

• Day 1 COUNT score would be 2, SUM score would be 3
• Day 2 COUNT score would be 4, SUM score would be 18
• Visualization would show an average of (3+18)/(2+4)=3.5 for the month

The latter is obviously the correct since it's not an average of averages but the actual average of all data.

The reason you have to use SUM Time series instead of average is to sum all COUNT and sum all SUM scores instead of averaging them.

You can apply the time series in the formula indicator but it makes more sense to leave that and apply it on the widget/visualization, so that the same formula indicator can be re-used for different aggregations.