How to graph multiple time series using postgresql

Grafana Time Series Panel
1

Hi,

Here is an example dataset that corresponds to 2 points in time:

  netelement   | in_ifname | is_ipv4 |     bps     |     metered_on      
---------------+-----------+---------+-------------+---------------------
 de-dus23      | ae5.0     | t       | 1.08276e+10 | 2017-12-01 15:00:00
 de-dus23      | ae7.0     | f       |  1.9916e+07 | 2017-12-01 15:00:00
 de-dus23      | ae7.0     | t       | 1.10909e+10 | 2017-12-01 15:00:00
 de-fra01      | ae33.0    | f       | 5.16979e+07 | 2017-12-01 15:00:00
 de-fra01      | ae33.0    | t       | 3.79882e+10 | 2017-12-01 15:00:00
 nl-ams04      | ae21.0    | f       |   1.563e+08 | 2017-12-01 15:00:00
 nl-ams04      | ae21.0    | t       | 4.20997e+10 | 2017-12-01 15:00:00
 uk-lon01      | ae7.0     | f       | 8.17707e+06 | 2017-12-01 15:00:00
 uk-lon01      | ae7.0     | t       | 6.35056e+09 | 2017-12-01 15:00:00
 uk-lon01      | ae8.0     | t       |  5.7851e+09 | 2017-12-01 15:00:00
 de-dus23      | ae5.0     | t       |  1.0762e+10 | 2017-12-01 14:55:00
 de-dus23      | ae7.0     | f       |  3.7552e+07 | 2017-12-01 14:55:00
 de-dus23      | ae7.0     | t       | 1.09573e+10 | 2017-12-01 14:55:00
 de-fra01      | ae33.0    | f       | 8.92227e+07 | 2017-12-01 14:55:00
 de-fra01      | ae33.0    | t       | 4.75239e+10 | 2017-12-01 14:55:00
 nl-ams04      | ae21.0    | f       | 1.97737e+08 | 2017-12-01 14:55:00
 nl-ams04      | ae21.0    | t       | 5.20107e+10 | 2017-12-01 14:55:00
 uk-lon01      | ae7.0     | f       | 1.51736e+07 | 2017-12-01 14:55:00
 uk-lon01      | ae7.0     | t       | 7.79276e+09 | 2017-12-01 14:55:00
 uk-lon01      | ae8.0     | t       | 7.04485e+09 | 2017-12-01 14:55:00

produced by the following postgresql query

SELECT ix_terse.netelement, ix_terse.in_ifname, ix_terse.is_ipv4, SUM(ix_terse.bps) AS bps, ix_terse.metered_on
FROM app.ix_terse
WHERE peer_as=714 
GROUP BY ix_terse.netelement, ix_terse.in_ifname, ix_terse.is_ipv4, ix_terse.metered_on 
ORDER BY ix_terse.metered_on DESC ;

Could you please help me and advise how-to graph a multiple time series graph panel and
each time series will have a name that is derived from the concatenating the columns ix_terse.netelement, ix_terse.in_ifname, ix_terse.is_ipv4 from the GROUP BY statement (in other words) ?

The related wrong Grafana query that I am seeking help for is :

SELECT SUM(ix_terse.bps) AS bps, $__time(ix_terse.metered_on)
FROM pmacct.ix_terse
WHERE $__timeFilter(ix_terse.metered_on) AND peer_as=714
GROUP BY ix_terse.netelement, ix_terse.in_ifname, ix_terse.is_ipv4, ix_terse.metered_on 
ORDER BY ix_terse.metered_on DESC ;

Niko

2

Solved :slightly_smiling_face:

SELECT CONCAT_WS('_', ix_terse.netelement, ix_terse.in_ifname, 
(CASE WHEN ix_terse.is_ipv4 IS TRUE THEN '4' ELSE '6' END)) AS metric, 
SUM(ix_terse.bps) AS bps, $__time(ix_terse.metered_on)
FROM app.ix_terse
WHERE $__timeFilter(ix_terse.metered_on) AND peer_as=$asn
GROUP BY ix_terse.netelement, ix_terse.in_ifname, ix_terse.is_ipv4, ix_terse.metered_on 
ORDER BY ix_terse.metered_on DESC ;