Need help joining data from multiple sources (prometheus) and multiple fields

I have prometheus running on multiple synology servers. I’m trying to build a dashboard that combines the data from all three. As an example, I created four queries - two for two servers/fields: modelName and version. See attached below:

The data is as follows and how I would like to be transformed:
ModelName Version
RS1221+ DSM 7.2-72806
RS1619xs+ DSM 7.2-72806

I tried to merge the data using merge series/tables but I get the data on different rows. I can join on a common field of Time but that also doesn’t seem to yield the expected results. I’m at a loss on how to make this work (grafana noob).

Here’s the screenshot of the merge:

Any ideas would be greatly appreciated.

Hi,

I think you should be better off with joining the queries in the datasource - something like:

modelName{job=“snmp-docker”} + on(job) (version{job=“snmp-docker”}*0)

as join of queries A and C (from DataVault) datasource

and

modelName{job=“snmp-docker”} + on(job) (version{job=“snmp-docker”}*0)

from MediaVault datasource. Then merge transformation should work

Thanks, @dawiddebowski for the quick response. I really appreciate it. When you say joining the queries in the datasource do you mean add that directly to the query?

I tried adding it, but I get an error. See attached:

Thanks!

Seems like some weird formatting of " signs. Try delete the " around snmp-docker - the query is right and should be working.

Okay. Removing the " around snmp-docker creates a different error, but replacing them with new quotes seems to get rid of the error. This is what the queries look like now:

When I try to merge this is what it looks like:

I feel like I’m missing something but not sure what it is.

Can you show the data without the transformation (cover sensitive data if you have some)? Each query should produce only one serie with all the labels from both metrics.

Sure. This is what I get when I don’t add a transformation.

Ok, sorry, I was a bit mistaken - when doing the join, the only labels that are prevailed in the result are the ones in on(...) expression and from the left / right side of the expression (if doing group_left, group_right) - I didn’t know that :sweat_smile:

What you can do is come back to your first solution with four queries, merge the tables (as you have in the original post) and then:

  1. Use Partition by values transformation - for field pick __name__ (as your metric name) - it will create two tables with respective metrics
  2. Use Join by field with mode OUTER (TABULAR) and as field pick instance (base field name) - it will join the tables by the common label - in your case - the instance.

Should work (for readability and hiding unnecessary columns you can use Organize fields by name transformation.

Thanks for the updated suggestion. I implemented your recommendations for my 3 servers. If I only apply this solution to two fields per server (modelName,Version) it seems to work as expected. However, if I include a third metric (serialNumber) then it seems to break (note: in this first screenshot I’m disabling the third metric)


This is what happens when I enable three metrics:

I need to be able to scale this up with multiple metrics.

Thanks!

I have yet to see someone being able to join multiple queries be in merge or join

The only way I have been able to do it was via business text and javascript

It seems like join mode OUTER (TIME SERIES) works (worked for my example), but I’m not sure why :person_shrugging:

1 Like

I tried switching to an outer join time series, but one of my severs disappears from the list.

Interesting. I was using this as my reference. I wanted to set up something similar but using a different snmp.yml file. I tried loading it but the data doesn’t appear.

They seem to have a lot of data merged into one panel.

My current dashboard is based on this tutorial from Mariushosting:

Which is great, but is only for a detailed look at one server at a time. I wanted to create a second dashboard that creates an overview.

1 Like