downsample algorithm

[AnalogJ]

Outstanding Tasks

• Fix tests (mock out the scrutiny repository and use it in tests)
• Change queries so they query multiple buckets from InfluxDB.
• Ensure that downsampled data still works in the UI and is correctly decoded.

Links to InfluxDB downsampling

• https://machinelearningmastery.com/resample-interpolate-time-series-data-python/
• https://community.influxdata.com/t/how-do-i-downsample-data-using-multiple-operations-within-the-same-bucket-in-influxdb-2-0/16042/3
• https://docs.influxdata.com/influxdb/v2.0/process-data/common-tasks/downsample-data/
• https://www.influxdata.com/blog/downsampling-influxdb-v2-0/
• https://docs.influxdata.com/influxdb/cloud/process-data/common-tasks/downsample-data/
• https://community.influxdata.com/t/flux-query-over-multiple-downsampled-buckets-with-different-retention-policies/14652
• https://docs.influxdata.com/influxdb/v1.8/guides/downsample_and_retain/
• https://www.influxdata.com/blog/trending-aggregate-values-by-downsampling-with-influxdb/
• http://wiki.webperfect.ch/index.php?title=InfluxDB:_Downsampling
• https://dganais.medium.com/data-layout-and-schema-design-best-practices-for-influxdb-a2a5d9e300c0
• https://sqlpac.com/en/documents/influxdb-v2-flux-language-quick-reference-guide-cheat-sheet.html
• https://www.influxdata.com/blog/flux-aggregation-influxdb-now-or-later/
• https://community.influxdata.com/t/aggregatewindow-for-multiple-columns/18468/2
• https://docs.influxdata.com/flux/v0.x/function-types/
• Simple aggregating task using mean errors influxdata/influxdb#11488
• https://www.influxdata.com/blog/flux-windowing-and-aggregation/

[AnalogJ]

@bergernetch said:

---

You could use influx, can handle this by itself.

[AnalogJ]

hmm. it would be interesting to only store the last snapshot of SMART data in sqlite, and use influx for all historical data... then I only really need to pull it up for the details page (and temp graph on dashboard)

Discussed in Linuxserver.io chat: https://discordapp.com/channels/354974912613449730/506828136869265408/756938882657943612

[AnalogJ]

related issues:

• [BUG] Response size from /api/summary can grow to enormous sizes, crash things #177
• [BUG] WebUI is slow when collecting SMART data quite regularly #158

[AnalogJ]

potentially working

  import "influxdata/influxdb/schema"
  data = from(bucket: "metrics")
  |> range(start: -2y, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "smart" )
  |> filter(fn: (r) => r["device_wwn"] == "0x5000cca264eb01d7" )
  |> filter(fn: (r) => r["_field"] !~ /(raw_string|_measurement|device_protocol|device_wwn|attribute_id|name|status|when_failed)/)
  |> aggregateWindow(fn: mean, every: 1y)
  |> schema.fieldsAsCols()
  |> yield(name: "last")

[AnalogJ]

influxdata/flux#2159 has been merged, we can re-write the aggregation logic and do type specific aggregation now .

https://github.com/influxdata/flux/pull/4366/files

        testing.load(tables: tableData)
            |> range(start: -100)
            |> filter(fn: (r) => types.isType(v: r._value, type: "string"))
            |> drop(columns: ["_start", "_stop"])

https://docs.influxdata.com/flux/v0.x/stdlib/types/istype/#aggregate-or-select-data-based-on-type

data = () => from(bucket: "example-bucket")
    |> range(start: -1m)

nonNumericData = data()
    |> filter(fn: (r) => types.isType(v: r._value, type: "string") or types.isType(v: r._value, type: "bool"))
    |> aggregateWindow(every: 30s, fn: last)

numericData = data()
    |> filter(fn: (r) => types.isType(v: r._value, type: "int") or types.isType(v: r._value, type: "float"))
    |> aggregateWindow(every: 30s, fn: mean)

> union(tables: [nonNumericData, numericData])

[AnalogJ]

working

https://www.reddit.com/r/influxdb/comments/s4zb80/aggregatewindow_with_mixed_types/

import "influxdata/influxdb/schema"
import "types"

sourceBucket = "metrics"
rangeStart = -2w
rangeEnd = -1w
aggWindow = 1w
destBucket = "metrics_weekly"
destOrg = "scrutiny"

smart_data = from(bucket: sourceBucket)
|> range(start: rangeStart, stop: rangeEnd)
|> filter(fn: (r) => r["_measurement"] == "smart" )
|> group(columns: ["device_wwn", "_field"])


nonNumericData = smart_data
    |> filter(fn: (r) => types.isType(v: r._value, type: "string") or types.isType(v: r._value, type: "bool"))
    |> aggregateWindow(every: aggWindow, fn: last, createEmpty: false)

numericData = smart_data
    |> filter(fn: (r) => types.isType(v: r._value, type: "int") or types.isType(v: r._value, type: "float"))
    |> aggregateWindow(every: aggWindow, fn: mean, createEmpty: false)

union(tables: [nonNumericData, numericData])
|> schema.fieldsAsCols()
|> yield()