Query Help With Using Math In Selection

I have the following queries in a Time Series graph. The first query is essentially showing a increasing line throughout the time span. The second query is a single point at the beginning of the graph.

What I was trying to achieve in the third query was taking the ("rx_bytes" + "tx_bytes") amount and subtract it by (first("rx_bytes") + first("tx_bytes")).

Anyway to achieve this?

Datasource: InfluxDB

Works
SELECT ("rx_bytes" + "tx_bytes") FROM "usg_wan_ports" WHERE ("device_name" = 'ID' AND "site_name" = 'Default (default)') AND $timeFilter GROUP BY "purpose"

Works
SELECT (first("rx_bytes") + first("tx_bytes")) FROM "usg_wan_ports" WHERE ("device_name" = 'ID' AND "site_name" = 'Default (default)') AND $timeFilter GROUP BY "purpose"

error:“mixing multiple selector functions with tags or fields is not supported”
SELECT ("rx_bytes" + "tx_bytes" - first("rx_bytes") - first("tx_bytes")) FROM "usg_wan_ports" WHERE ("device_name" = 'ID' AND "site_name" = 'Default (default)') AND $timeFilter GROUP BY "purpose"

@jangaraj / @melrose Any ideas?

if you go iteratively when is the error coming

Not sure how I would check iteratively. However, when viewing the Query Inspector this is the output.

{
  "request": {
"method": "GET",
"url": "api/datasources/proxy/3/query",
"params": {
  "db": "unifidreammachinepro",
  "q": ";SELECT (\"rx_bytes\" + \"tx_bytes\" - first(\"rx_bytes\") - first(\"tx_bytes\")) FROM \"usg_wan_ports\" WHERE (\"device_name\" = 'ID' AND \"site_name\" = 'Default (default)') AND time >= 1614578400000ms and time <= now() - 5s GROUP BY \"purpose\"",
  "epoch": "ms"
},
"data": null,
"precision": "ms",
"hideFromInspector": false
  },
  "response": {
"results": [
  {
    "statement_id": 0,
    "error": "mixing multiple selector functions with tags or fields is not supported"
  }
],
"executedQueryString": ";SELECT (\"rx_bytes\" + \"tx_bytes\" - first(\"rx_bytes\") - first(\"tx_bytes\")) FROM \"usg_wan_ports\" WHERE (\"device_name\" = 'ID' AND \"site_name\" = 'Default (default)') AND time >= 1614578400000ms and time <= now() - 5s GROUP BY \"purpose\""
  }
}

looks strange, what version of graphana do you use?

Grafana v7.3.6 (ea06633c34)

It’s not a Grafana issue, but your InfluxDB query issue. You are trying to use aggregated value when it’s not available, which doesn’t make sense, so InfluxDB returns error. I would use subqueries:

SELECT <operations with a,b,c,d values from inner query>
FROM (
  SELECT 
    rx_bytes AS a, 
    tx_bytes AS b, 
    first(rx_bytes) AS c, 
    first(tx_bytes) AS d
  FROM ...
)
...

Please don’t tag me in your requests in the future. It’s not very polite. Any help here is non-claimable and people giving you advices free of charge in their free time (IMHO even non sense advices as you see in the previous responses). Let’s people decide if they want you to help or not on their own.

I have Grafana v7.4.3 and also use Influx, and have a similar setup with query A, query B, and query C (which subtracts the results of query B from query A), and it works. Here is what my 3 queries look like:

Query A:
SELECT mean(“PRESENT_VALUE”) FROM “stations” WHERE $timeFilter GROUP BY time(1m) fill(null)

Query B:
SELECT mean(“SETPOINT_VALUE”) FROM “stations” WHERE $timeFilter GROUP BY time(1m) fill(null)

Query C:
SELECT mean(“PRESENT_VALUE”) - mean(“SETPOINT_VALUE”) AS “DIFFERENCE” FROM “stations” WHERE timeFilter GROUP BY time(__interval) fill(null)

Thanks for the suggestion.

I tested multiple variations of your suggestion. As seen below, anytime the first() aggregator is used things seem to fall apart. Is the first(var) selector just grabbing the first value it finds? I assume thats what its doing but I don’t see why its causing such a issue…

Results

A full time series graph is displayed

SELECT a+b
FROM (
  SELECT 
    rx_bytes AS a, 
    tx_bytes AS b
  FROM usg_wan_ports WHERE ("device_name" =~ /^$host$/ AND "site_name" =~ /^$Site$/) AND $timeFilter GROUP BY "purpose"
)

A single point is displayed on the time series graph

SELECT a+b
FROM (
  SELECT 
    rx_bytes AS a, 
    tx_bytes AS b,
    first(rx_bytes) AS c
  FROM usg_wan_ports WHERE ("device_name" =~ /^$host$/ AND "site_name" =~ /^$Site$/) AND $timeFilter GROUP BY "purpose"
)

Error: mixing multiple selector functions with tags or fields is not supported

SELECT a+b
FROM (
  SELECT 
    rx_bytes AS a, 
    tx_bytes AS b,
    first(rx_bytes) AS c,
    first(tx_bytes) AS d
  FROM usg_wan_ports WHERE ("device_name" =~ /^$host$/ AND "site_name" =~ /^$Site$/) AND $timeFilter GROUP BY "purpose"
)

:man_facepalming: that was very simplified idea, not a copy/paste solution. Why should I spend ~x minutes with developing solution for you for free, which you will just copy&paste? I would recommend to contact some paid support if you really need that.

Again:

SELECT <operations with a,b,c,d values from inner query>
FROM (
  SELECT 
    rx_bytes AS a, 
    tx_bytes AS b, 
    first(rx_bytes) AS c, 
    first(tx_bytes) AS d
  FROM ...
)
...

‘…’ → add there what you need - InfluxDB doc is your good friend (because it is a InfluxDB query issue and not really Grafana issue). Clue Data exploration using InfluxQL | InfluxDB OSS 1.7 Documentation Develop&test&try&iterate until you have desired result.

Yes I get that, was just reporting the findings from before as having 2 or move of those aggregators is what caused the issue.

@jangaraj :man_facepalming: that was very simplified idea, not a copy/paste solution. Why should I spend ~x minutes with developing solution for you for free, which you will just copy&paste?

Your statement above is not exactly appropriate and has a lot of resistment in it. We all understand what a community forums is and what that entails. Not to mention the tagging feature exists so you can specifically mention other users. That feature would not exist if it was unnecessary. Everyone appreciates each others time here. Those that truly love the product that their working with would want to “spread the good word” around for others and that can be seen by the forums.

I did manage to get subqueries to work (kind of).

I was expecting the “frxtx” to be a linear line and not a single point. So looking into that currently.

SELECT "mrxtx", "frxtx" FROM 
 ( SELECT mean("rx_bytes") + mean("tx_bytes") AS "mrxtx" FROM "unifidreammachinepro"."autogen"."usg_wan_ports" WHERE time > :dashboardTime: AND time < :upperDashboardTime: GROUP BY time(:interval:) FILL(null) ),
 ( SELECT first("rx_bytes") + first("tx_bytes") AS "frxtx" FROM "unifidreammachinepro"."autogen"."usg_wan_ports" WHERE time > :dashboardTime: AND time < :upperDashboardTime: GROUP BY null FILL(previous) )


InfluxDB Chronograph for testing InfluxQL Queries

This topic was automatically closed after 365 days. New replies are no longer allowed.