I'm struggling trying to visualize a Metric visualization with a given weighted average over a certain timespan. Let's consider the following scenario:
fields of interest: day (date), shop_id (text), shortmessage (text)
sample log: 2020-11-11 | X | foo
possible values of shop_id: (X, Y, Z).
possible values of shortmessage: (foo, bar, whatever)
Let's consider the following situation concerning some collected logs, all of them with
2020-11-06: 3 affected shop_ids, 47 total events (X=14, Y=14, Z=19) 2020-11-07: 0 affected shop_ids, 0 total events (X=0, Y=0, Z=0) 2020-11-08: 2 affected shop_ids, 55 total events (X=23, Y=32, Z=0) 2020-11-09: 2 affected shop_ids, 29 total events (X=15, Y=0, Z=14) 2020-11-10: 3 affected shop_ids, 38 total events (X=11, Y=15, Z=12) 2020-11-11: 3 affected shop_ids, 30 total events (X=12, Y=6, Z=12) 2020-11-12: 3 affected shop_ids, 50 total events (X=21, Y=11, Z=18)
Average number of "foo" events by shop_id per day:
((47/3) + 0 + (55/2) + (29/2) + (38/3) + (30/3) + (50/3)) / 7 = 13.85
Obviously, other than weight the average dividing each daily total by the number of the shop_ids affected that day, it also has to consider those days where no shop_id has been affected (for example those days where no "foo" shortmessage has been ingested).
Furthermore, I'd like to have another visualization (Table or TopN for example) listing all those shop_ids whose average over the same timespan is greater than that 13.85. In this specific case:
X --> (14+0+23+15+11+12+21)/7 = 13.71 Y --> (14+0+32+0+15+6+11)/7 = 11.14 Z --> (19+0+0+14+12+12+18)/7 = 10.71
none of them would be visualized since they're all below 13.85 threshold.
How can I build such visualizations? I guess using something like Bucket Script or Math in the TSVB but I cannot find the right settings. Possibly I might have to mix something like the Cardinality with the Overall Average or Moving Average and compute the weighted part with a Bucket Script, but I cannot find the right syntax nor the right Interval settings to set in the Panel Options to make it work.