I have 2 collectors - telegraf and MRTG. Telegraf collects SNMP metrics for my network devices and it collects the data in raw format; i.e. ifHCInOctets is collected as the counter it is, while MRTG collects the data already processed as a derivative. Because of this, I have stored MRTG data in a measurement called snmp, while Telegraf data is collected under measurement telegraf_snmp. The issue I have now is when it comes to charting the data using Grafana, there is no way to graphically display both queries as a single legend - grafana will display 2 legends, the tooltip will also show as 2 different values.
The solution I thought it would resolve this was to combine both measurements and generate a single output by running a query such as the one that follows:
SELECT sum(*) FROM ( SELECT mean("ifHCInOctets") as "mean" FROM "snmp" WHERE ("agent_host" =~ /^rtr1$/ AND "ifName" =~ /^Ethernet1\/11$/) AND time >= 1598970930854ms and time <= 1598987517707ms GROUP BY time(1m) ), (SELECT non_negative_derivative(mean("ifHCInOctets"),1s) AS "mean" FROM "telegraf_snmp" WHERE "agent_host" =~ /^rtr1$/ AND "ifName" =~ /^Ethernet1\/11$/ AND time >= 1598970930854ms and time <= 1598987517707ms GROUP BY time(1m) ) GROUP by time(1h)
But unfortunately this does not generate a single output but 2 outputs:
name: snmp
time sum_mean
---- --------
1598968800000000000 1820567.1800000002
1598972400000000000 7382995.090000001
...
name: telegraf_snmp
time sum_mean
---- --------
1598968800000000000 1796329.32
1598972400000000000 7403558.716666667
1598976000000000000 12098341.25
1598979600000000000 5369634.243333334
...
I also tried doing as follows which generated neither error nor output
SELECT "snmp"."mean" + "telegraf_snmp"."mean" FROM ( SELECT mean("ifHCInOctets") as "mean" FROM "snmp" WHERE ("agent_host" =~ /^rtr1$/ AND "ifName" =~ /^Ethernet1\/11$/) AND time >= 1598970930854ms and time <= 1598987517707ms GROUP BY time(1m) ), (SELECT non_negative_derivative(mean("ifHCInOctets"),1s) AS "mean" FROM "telegraf_snmp" WHERE "agent_host" =~ /^rtr1$/ AND "ifName" =~ /^Ethernet1\/11$/ AND time >= 1598970930854ms and time <= 1598987517707ms GROUP BY time(1m) )
Any ideas how to accomplish this via influxql? or even if telegraf has a means to generate derivative data rather than collecting and posting the raw counter value (though I get that it is the query language that would do the manipulation of the raw data)?
