Chaining mixed queries from different data sets

Grafana Bar Gauge Panel
1
  • What Grafana version and what operating system are you using?

Grafana 9.3.1, open source, Linux OS unknown version (not an admin there).

  • What are you trying to achieve?

Due to scale issues, the VictoriaMetrics database is only keeping one label of interest, basically an asset ID, along with the metrics collected (there are a few more labels, but none that matter here). Everything else (product type, account information, …) is now kept in a separate Postgres database. Idea being, if we have the asset ID, we can look that asset ID up in Postgres to determine its product type, account information, etc. So far so good. But because this information isn’t a label any more, things like count (by label) don’t work. So I was hoping to get the values from the VictoriaMetrics query, take the asset label from that query and then do counts on the other fields now in Postgres (product type, account, …), and graph those counts (or sums, or…). Applies to any metric.

  • How are you trying to achieve it?

Was hoping this would be obvious from a mixed panel configuration, with query A being Prometheus to get the metric data along with asset label and query B being Postgres to do the other lookups, act on (count, sum, …) those other lookups, and graph those results.

Something like:

Query A/VictoriaMetrics:
(metric){}[24h])

to return a list of results with the ‘asset ID’ label.

Query B/Postgres:
select name from table where asset_id in [query A results]
or do counts, etc. But I’m still trying to figure out how to specify the query A results in the query B syntax.

  • What happened?

Nothing yet, not seeing how to have mixed queries from different data sources interact with each other.

  • What did you expect to happen?

A nice easy UI to just click here and there and have it make sense?

  • Can you copy/paste the configuration(s) that you are having problems with?

No.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

No errors, trying to figure out how to make this work still.

  • Did you follow any online instructions? If so, what is the URL?

Looked at related questions here. Not sure how URL variables will help, doesn’t look like the Merge transform will work either.

2

Are the values from vicky gonna be presented as drop down?

3

Other way around, eventually. Variables for filtering results will be from Postgres. I’m just trying to figure out how to take output of one query (list of all assets from VictoriaMetrics) and feed that as input to another (Postgres) to process. Ideally I’d like to do filtering in query A so I can just do processing in query B, but I don’t know how to do that … unless I can transform variables into labels, or if PromQL had another syntax for that.

4

This is confusing. What is feeding what variables to whom

5

It may help to stop thinking about variables.

I want query A (VictoriaMetrics) in a given panel in a given dashboard to feed it’s RESULTS to query B (Postgres), so query B can then act on those results (filter, count, etc.), and then determine what gets visualized.

1 Like
6

True. But actually variables in this case hidden variable might be the route to go.

Then on postgres side you can do a where

Where assetId in ($vicassetIds)
7

Yeah, OK, thanks, that does look promising. But unless there’s a way to take the output of a query in a panel configuration and assign to that variable (which I’m missing in the UI or searching the internet), that would mean I’d have to create hidden variables that would contain metric results for any related visualization I’m trying to do… Might be doable though. Or am I still being a little slow?

8

you are on the track. Here is an example using sql server. the name of the variable is $names

image

image
image1344×441 29.8 KB

9

Well, this kind of works. The hidden variable storing the results of a query can’t actually be hidden, though. Rather, it has to always be set to All (might be a way to tweak the JSON to force this, haven’t looked yet) and that requires it to be visible so someone can do that, as it seems to default to just the first result.

And while this works for some cases, whenever I need to do calculations on the metric value vs. just seeing if the metric name exists or not, this idea breaks down. The variable trick just stores names, not actual values. Useful for counting those assets, but not if I wanted to know what the optimized or passthrough throughput of, say, interface X, application port Y was on that asset. Even if you could store metric values in a variable, that still presents the problem of how to get that metric data into some kind of visualization.

Looks like we either need to figure out how to maintain labels in our time series data without continuously running out of memory (another issue, being worked on by one of my colleagues), or maybe there’s some way to turn the information in Postgres into labels so I can still do the PromQL that worked before without the overhead of actually having those tables in the time series data. The transforms I’ve found that mention ‘label’ don’t do that though.

10

Yes it can be hidden, but maybe it depends on what version you are on

Your requirements keep shifting. Now you want same values you want to use as variable to also be visualized?

Concussed again

11

My requirements have never shifted. Trying to explain them in terms that more align with attempts to solve them doesn’t mean anything has changed. The ultimate and original goal of being able to pass results of one query to another on a different database for more processing and/or filtering doesn’t seem feasible now. It was a long shot anyway. Some acceptable compromises may come of this but some things don’t yet seem be possible. I do appreciate your suggestions as that did lead to some other avenues to try.

1 Like
12

It is feasible. Happy journey