This shows you the differences between two versions of the page.
geek:grafana [2020/04/20 00:05] admin created |
geek:grafana [2020/04/20 00:34] admin |
||
---|---|---|---|
Line 2: | Line 2: | ||
==== Kilobytes per second ==== | ==== Kilobytes per second ==== | ||
- | From my [[Projects/load logger]] project I collect the number of bytes in/out of my network devices. If I want to graph that in grafana using my MySQL data source (as KB/s) then I have to use SQL to munge the numbers: | + | From my [[Projects/load logger]] project I collect the number of bytes in/out of my network devices. If I want to graph that in grafana using my MySQL data source (as KB/s) then I have to use SQL to munge the numbers. This gets what I need in SQL: |
<code sql> | <code sql> | ||
select | select | ||
Line 8: | Line 8: | ||
created_on, | created_on, | ||
n4_r, | n4_r, | ||
- | ((n4_r - @lastValue) / 1024 )/60 as AvgKbPerSec, | + | ((n4_r - @lastValue) / 1024 ) as AvgKbPerSec, |
@lastValue := n4_r | @lastValue := n4_r | ||
from | from | ||
Line 15: | Line 15: | ||
order by | order by | ||
created_on,id | created_on,id | ||
+ | </code> | ||
+ | |||
+ | Then to get it working with Grafana, I change it to this (wrapped in an extra select, because graphana only wants one value to graph): | ||
+ | <code sql> | ||
+ | select time, KbPerSec from ( | ||
+ | select | ||
+ | created_on as "time", | ||
+ | if(@lastValue = 0, 0, ((n4_r - @lastValue) / 1024 )) as Kb, | ||
+ | @lastValue := n4_r | ||
+ | from | ||
+ | data_logger, | ||
+ | ( select @lastValue := 0 ) SQLVars | ||
+ | where $__timeFilter(created_on) | ||
+ | order by | ||
+ | created_on) as newtable | ||
+ | where $__timeFilter(time) | ||
+ | order by | ||
+ | time | ||
</code> | </code> |