Many-to-one join / lookup against another query

Grafana Dashboards
1
  • What Grafana version and what operating system are you using?

9.5.2 on Ubuntu

  • What are you trying to achieve?

I have two datasets. One, let’s call it the “logs”, has two columns, SSH fingerprint and login time. It has many entries, as one fingerprint logs in many times.

Fingerprint Time
abcdefg 00:02
abcdefg 00:03
uvwxyz 00:04
abcdefg 00:05
abcdefg 00:06

The other, let’s call it the “names”, has two columns, SSH fingerprint and human name. It has only a few entries, maybe two or three.

Fingerprint Name
abcdefg Joe
uvwxyz Mike

I want a panel/transformation that, given these two datasets, outputs a table of logins by username. That is, each entry in logs has its fingerprint looked up against / joined against names. (I guess this would resemble a LEFT JOIN? My SQL is rusty!)

Fingerprint Time Name
abcdefg 00:02 Joe
abcdefg 00:03 Joe
uvwxyz 00:04 Mike
abcdefg 00:05 Joe
abcdefg 00:06 Joe
  • How are you trying to achieve it? / What happened? / What did you expect to happen?

I’ve tried Join by Field, which seems like the most obvious solution, but it doesn’t appear to work at all - it only outputs two rows, one for each fingerprint. I would expect each fingerprint to occur multiple times, once for each logs entry it is joined against.

I’ve tried Merge, but it doesn’t seem to work at all - there’s no change in the output, and documentation is scarce.

Any help would be appreciated. I’ve done a bunch of Googling and the consensus practically seems to be that this isn’t possible (barring clever SQL workarounds that don’t really apply to my data source), which is surprising - it seems like a pretty basic use-case.

Thank you!

2

@n_b Take a look at the Dynamic Text panel. It may be possible to implement the required logic with Handlebars and Markdown instead of using transformations.

1 Like
3

Thank you! I’ll give this a try. Will post to the thread if I manage to solve it with this.

1 Like