Show from mySQL table containing text "xxx.xxx.xxx.xxx" (ip adress) in a table

Grafana MySQL
1

I got trouble showing IP address in a plain table. There is no error, but the table show up empty. Any idea what wrong with my query?

Its a json extract query and the table contains a lot more info, but its only the IP i can’t extract.
Result in prompt is ok (below), but not in Grafana, where i just get an empty table.

mysql> select attributes->’$.ip’ from deviceinfo;

| “178.232.147.109” |
| “178.232.147.109” |
| “178.232.147.109” |
±-------------------+
6694 rows in set (0,13 sec)

I can successfully extract anything but the IP adress from content below using previous command.

Here is the reply on the generic query:

select attributes from deviceinfo;

| {“priority”:0,“sat”:12,“event”:253,“di1”:1,“io200”:0,“io8”:45,“io90”:0,“io5”:80,“io91”:“ip”:“178.232.147.109”,“motion”:true}

2

Hi,

Please check the query inspector in Grafana for your query and check what kind of data that is returned in the response, if ip is included please paste the response here. Could also be that you need to add a column style for ip address so that is rendered as string.

Marcus

3

Its a null only in the reply, leading me to suspect that it doesn’t handle the " "
;
;
from Granfana:

sql:"SELECT UNIX_TIMESTAMP(devicetime) as time_sec, coalesce(attributes->’$.ip’, ‘’ ) as value, deviceId as metric FROM deviceinfo WHERE deviceId = 1 ORDER BY (devicetime) ASC"
series:Array[1]
0:Object
name:"1"
points:Array[4254]
0:Array[null,1516913348000]
1:Array[null,1516932415000]
2:Array[null,1516948637000]
3:Array[null,1516949029000]

;
;
Wheras querying for something else is OK
;
;
sql:"SELECT UNIX_TIMESTAMP(devicetime) as time_sec, coalesce(attributes->’$.io5’, ‘’ ) as value, deviceId as metric FROM deviceinfo WHERE deviceId = 1 ORDER BY (devicetime) ASC"
series:Array[1]
0:Object
name:"1"
points:Array[4254]
0:Array[null,1516913348000]
1:Array[null,1516932415000]
2:Array[null,1516948637000]
3:Array[50,1516949029000]
4:Array[65,1516950647000]
5:Array[60,1516950948000]

4

Since you’re using the table panel you shouldn’t need to use the column name returned as value. I think there may be some special handling of value column for time-series queries. So try and return a column named ip for example.

Also make sure that Format as table is selected:
image

Also make sure that you have selected table transform table under the options tab.

Marcus

5

Hi Marcus

That did the trick, but also it made the timestamp column epoch time, unreadable for human.

6

Great.

Think you need to name the timestamp column returned from query Time. Then the default column style will apply and format the column as date. If you add a custom column styles you can return basically any name of your columns. You also need to skip the UNIX_TIMESTAMP, i.e.

SELECT devicetime as Time

Marcus

7

Thanks for the tip Marcus, that made the timestamp more sensible.

I know it’s probably should be in the manual somewhere, but do you know how to query for only the last 5 freshest rows?

cheers,

8

Please read the manual of mysql. You’ll use the limit keyword, i.e.

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Where number would be 5 in your case.

For reference, please see Using MySQL in Grafana documentation.

Marcus

1 Like