Orga, Bürokraten, Administratoren
770
Bearbeitungen
StatusUpdates: Unterschied zwischen den Versionen
influx queries
Seth0r (Diskussion | Beiträge) (influx doku) |
Seth0r (Diskussion | Beiträge) (influx queries) |
||
| Zeile 86: | Zeile 86: | ||
GROUP BY time(1h), network fill(none) | GROUP BY time(1h), network fill(none) | ||
END | END | ||
</pre> | |||
Außerdem gibt es ein paar Queries, die regelmäßig via Cron ausgeführt werden, weil sie als CQ zu lange brauchen oder nicht funktionieren: | |||
<br> | |||
Täglich: | |||
<pre> | |||
# TRAFFIC TX, freifunk.network -> ffarchive.traffic_tx (5m von -48h) | |||
SELECT non_negative_derivative(last(tx_bytes)) AS tx_bytes | |||
INTO ffarchive."default".traffic_tx | |||
FROM freifunk."default".network | |||
WHERE time > NOW() - 48h | |||
GROUP BY time(5m), device, hostname fill(none) | |||
# TRAFFIC RX, freifunk.network -> ffarchive.traffic_rx (5m von -48h) | |||
SELECT non_negative_derivative(last(rx_bytes)) AS rx_bytes | |||
INTO ffarchive."default".traffic_rx | |||
FROM freifunk."default".network | |||
WHERE time > NOW() - 48h | |||
GROUP BY time(5m), device, hostname fill(none) | |||
# LQ NLQ, freifunk.links -> ffarchive.links (5m von -48h bis -23h) | |||
SELECT | |||
min(linkQuality) AS lq_min, | |||
max(linkQuality) AS lq_max, | |||
mean(linkQuality) AS lq_mean, | |||
median(linkQuality) AS lq_median, | |||
min(neighborLinkQuality) AS nlq_min, | |||
max(neighborLinkQuality) AS nlq_max, | |||
mean(neighborLinkQuality) AS nlq_mean, | |||
median(neighborLinkQuality) AS nlq_median | |||
INTO ffarchive."default".links | |||
FROM freifunk."default".links | |||
WHERE time < now() - 23h and time > NOW() - 48h | |||
GROUP BY time(5m), localIP, remoteIP, remoteHostname, hostname fill(none) | |||
# LEASES, freifunk.dhcp -> ffarchive.dhcp (5m von -48h bis -23h) | |||
SELECT min(leases) AS min, max(leases) AS max, mean(leases) AS mean, median(leases) AS median | |||
INTO ffarchive."default".dhcp | |||
FROM freifunk."default".dhcp | |||
WHERE time < now() - 23h and time > NOW() - 48h | |||
GROUP BY time(5m), network, hostname fill(none) | |||
# ASSOC, freifunk.wireless -> ffarchive.wireless (5m von -3h) | |||
SELECT min(assoc) AS min, max(assoc) AS max, mean(assoc) AS mean, median(assoc) AS median | |||
INTO ffarchive."default".wireless | |||
FROM freifunk."default".wireless | |||
WHERE time < now() - 23h and time > NOW() - 48h | |||
GROUP BY time(5m), device, essid, hostname fill(none) | |||
# ETX, ffarchive.links -> ffarchive.link_etx (5m von -48h bis -23h) | |||
SELECT | |||
1 / (max(lq_max) * max(nlq_max)) AS etx_max, | |||
1 / (min(lq_min) * min(nlq_min)) AS etx_min, | |||
1 / (mean(lq_mean) * mean(nlq_mean)) AS etx_mean, | |||
1 / (median(lq_median) * median(nlq_median)) AS etx_median | |||
INTO ffarchive."default".link_etx | |||
FROM ffarchive."default".links | |||
WHERE time < now() - 23h and time > NOW() - 48h | |||
GROUP BY time(5m), localIP, remoteIP, remoteHostname, hostname fill(none) | |||
</pre> | |||
Stündlich: | |||
<pre> | |||
# TRAFFIC TX, freifunk.network -> ffarchive.traffic_tx (5m von -3h) | |||
SELECT non_negative_derivative(last(tx_bytes)) AS tx_bytes | |||
INTO ffarchive."default".traffic_tx | |||
FROM freifunk."default".network | |||
WHERE time > NOW() - 3h | |||
GROUP BY time(5m), device, hostname fill(none) | |||
# TRAFFIC RX, freifunk.network -> ffarchive.traffic_rx (5m von -3h) | |||
SELECT non_negative_derivative(last(rx_bytes)) AS rx_bytes | |||
INTO ffarchive."default".traffic_rx | |||
FROM freifunk."default".network | |||
WHERE time > NOW() - 3h | |||
GROUP BY time(5m), device, hostname fill(none)' | |||
# LEASES, freifunk.dhcp -> ffarchive.dhcp (5m von -3h) | |||
SELECT min(leases) AS min, max(leases) AS max, mean(leases) AS mean, median(leases) AS median | |||
INTO ffarchive."default".dhcp | |||
FROM freifunk."default".dhcp | |||
WHERE time > now() - 3h | |||
GROUP BY time(5m), network, hostname fill(none) | |||
# ASSOC, freifunk.wireless -> ffarchive.wireless (5m von -3h) | |||
SELECT min(assoc) AS min, max(assoc) AS max, mean(assoc) AS mean, median(assoc) AS median | |||
INTO ffarchive."default".wireless | |||
FROM freifunk."default".wireless | |||
WHERE time > now() - 3h | |||
GROUP BY time(5m), device, essid, hostname fill(none) | |||
# ETX, freifunk.links -> freifunk.link_etx (1m von -3h bis -1h) | |||
SELECT 1 / (mean(linkQuality) * mean(neighborLinkQuality)) AS etx | |||
INTO freifunk."default".link_etx | |||
FROM freifunk."default".links | |||
WHERE time > now() - 3h AND time < now() - 1h | |||
GROUP BY time(1m), localIP, remoteIP, remoteHostname, hostname fill(none) | |||
</pre> | </pre> | ||