Differences

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] (current)
admin
@@ Line 2: / Line 2: @@
 ==== 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>
 select
@@ Line 8: / Line 8: @@
       created_on,
       n4_r,
-      ((n4_r - @lastValue) / 1024 )/60 as AvgKbPerSec,
+      ((n4_r - @lastValue) / 1024 ) as AvgKbPerSec,
       @lastValue := n4_r
    from
@@ Line 15: / Line 15: @@
    order by
 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>

neil.mckillop.org