MYSQL query iterating trough multiple value variable

Grafana
, , ,
1

I created a script on my routers that sends the rx and tx of each interface to a php web server. The server takes care of the necessary calculations and sends everything back to a mysql database server.

The database looks like this:

+------------+------------+------------------------+------------+-----------+
| time       | agent_host | intName                | tx         | rx        |
+------------+------------+------------------------+------------+-----------+
| 1707911594 | 10.211.0.2 | LoopBr                 |  101335670 |         0 |

Working on the latest version of Grafana, this query:

SELECT sum(total_tx) 
FROM bandwidth 
WHERE agent_host =~ /^$routerIP$/
  AND time BETWEEN 
    (select MAX(time)
     from supervision.bandwidth) - 3000
    AND
    (select MAX(time)
     from supervision.bandwidth)

returns me the error:

db query error: Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '/^'10.211.0.2','10.211.0.3','10.211.0.4','10.211.0.5','10.211.0.6','10.211.0....' at line 3

image
image1199×337 26.9 KB

I’ve also tried the query:

SELECT sum(total_tx) 
FROM bandwidth 
WHERE agent_host =~ $routerIP
  AND time BETWEEN 
    (select MAX(time)
     from supervision.bandwidth) - 3000
    AND
    (select MAX(time)
     from supervision.bandwidth)

Which returns the same erroe (without the " /^ ").

Also tried:

SELECT SUM(total_tx) 
FROM supervision.bandwidth 
WHERE agent_host =~ '${routerIP:singlequote}'
  AND time BETWEEN 
    (select MAX(time)
     from supervision.bandwidth) - 3000
    AND
    (select MAX(time)
     from supervision.bandwidth)

or:

SELECT SUM(total_tx) 
FROM supervision.bandwidth 
WHERE agent_host =~ '$routerIP'
  AND time BETWEEN 
    (select MAX(time)
     from supervision.bandwidth) - 3000
    AND
    (select MAX(time)
     from supervision.bandwidth)

Which returns the same error

2

I’ve build a new variable MYSQLrouterIP using the mysql connection instead of the routerIP which was using the influxDB connection (I have two bases).

image

This solved the error problem, but the panel is returning a no data, as if the use of the variable were empty or other than the values it contains.

SELECT SUM(total_tx) 
FROM supervision.bandwidth 
WHERE agent_host =~ '{$MSQLrouterIP:singlequote}'
  AND time BETWEEN 
    (select MAX(time)
     from supervision.bandwidth) - 3000
    AND
    (select MAX(time)
     from supervision.bandwidth)

image
image1229×455 25.9 KB

3

What do you see when you click query inspector and click on refresh button on query tab

4

There:

SELECT SUM(total_tx) 
FROM supervision.bandwidth 
WHERE agent_host =~ '{$MSQLrouterIP:singlequote}'
  AND time BETWEEN 
    (select MAX(time)
     from supervision.bandwidth) - 3000
    AND
    (select MAX(time)
     from supervision.bandwidth)

image
image462×692 51.5 KB

5

So, do you see the problem with your query if you had to copy it from that query inspector and run it directly in mysql using for example mysql workbench?

6

I mean, of course I see the problem haha. It’s the solution I don’t see.

I’ve tried to use this solution of the doc:

Singlequote

Formats single- and multi-valued variables into a comma-separated string, escapes ' in each value by \' and quotes each value with '.

servers = ['test1', 'test2']
String to interpolate: '${servers:singlequote}'
Interpolation result: "'test1','test2'"

which is not a solution for my case since grafana is taking as value '{$MSQLrouterIP:singlequote}' instead of the content of this variable for each panel (I’m doing a repeat panel).

1 Like
7

:smile:What is =~ in mysql query? Rusty on mysql

8

Well, it does not exist, but I’ve started to work on influxDB base with Grafana, so I speculated that it might work the same way. At first, I’ve tried with agent_host = but got the same result as well…
I wanted to do the same as with my influxDB queries, which iterate trough each values of my variable using the =~.

9

:joy:

Ok in that case try

agent_host IN (VAR HERE)

But also keep tweaking that variable and checking inspector

1 Like
10

Finally, the solution was so “simple”…
Thank you a lot!

11

Would be good to share what is the solution

12

No need to create new variable or weird things, just use the correct syntax for MariaDB query:

SELECT SUM(total_tx) 
FROM supervision.bandwidth 
WHERE agent_host IN ($routerIP )
  AND time BETWEEN 
    (select MAX(time)
     from supervision.bandwidth) - 3000
    AND
    (select MAX(time)
     from supervision.bandwidth)
1 Like