Help With Joining Data

InfluxDB 2 Dashboards
1

I am using Grafana to create a table.

I understand that Influxdb doesn’t support traditional SQL joins, so I am trying to find another way to accomplish what appears to be a simple task. I have two measurements that have a single piece of data that overlaps between them. I tried putting everything into a single measurement with the SNMP plugin, but the data was still separated by SNMP tables, and it just made more sense to do different measurements. It’s pretty simple.

There’s a “wlc-ap-table” measurement that houses AP data from a Cisco WLC. The fields I care about are “AP-MAC” (the MAC of the AP) and “APGroup” (Group Name of the AP).

The second measurement is essentially the table of all the clients. It’s called “wlc-airespace-clients”. The only fields that matter are the “AirespaceMAC” (the MAC of the Client) and “AP-MAC”. The logic of the query is also straightforward:

For each APGroup Sum the Number of Clients Connected

My approach was to:

Query the client measurement and get a table of AirespaceMAC and AP-MAC.
Query the ap measurement and get a table of AP-MAC and APGroup.
Join the two to add the AirespaceMAC on the left to get AirespaceMAC | AP-MAC | APGroup.
From there, just do a Count on AirespaceMAC in each APGroup.

I would want the standard timestamp stuff like current, min, and max over the interval.

I know the logic of what I am trying to do, but I am super new to this, and I started trying to do it in Kapacitor, but it’s hard to get the logic down to run tests. I’m still reading up and learning. I’m about 10-15 hours into it at this point, and am pretty frustrated.

I got this far:

dbrp "telegraf"."autogen"

var clients = batch
    |query('SELECT "AP-MAC",("AirespaceMAC") FROM "telegraf"."a
utogen"."wlc-airespace-clients"')
        .period(30s)
        .every(30s)
        .groupBy()
        .fill(0)

var apgroups = batch
    |query('SELECT ("APGroup"),"AP-MAC" FROM "telegraf"."autoge
n"."wlc-ap-table"')
        .period(30s)
        .every(30s)
        .groupBy()
        .fill(0)

clients
    |join(apgroups).as('clients', 'groups')
    |influxDBOut().database('telegraf').measurement('combodata'
)

I finally got some data back but it’s not the right data unfortunately. It’s this:

> select * from combodata
name: combodata
time                clients.AP-MAC    clients.AirespaceMAC groups.AP-MAC     groups.APGroup
----                --------------    -------------------- -------------     --------------
1528085000000000000 00:fe:c8:d2:de:b0 30:59:b7:5b:61:d6    00:fe:c8:e0:13:40 default-group
1528085015000000000 00:fe:c8:d2:de:b0 30:59:b7:5b:61:d6    00:fe:c8:e0:13:40 default-group
1528085030000000000 00:fe:c8:d2:de:b0 30:59:b7:5b:61:d6    00:fe:c8:e0:13:40 default-group
1528085045000000000 00:fe:c8:d2:de:b0 30:59:b7:5b:61:d6    00:fe:c8:e0:13:40 default-group
1528085060000000000 00:fe:c8:d2:de:b0 30:59:b7:5b:61:d6    00:fe:c8:e0:13:40 default-group

It’s just the same thing in every record, and it’s not doing the lookups of one table into the other. I am doing more research, but all help is appreciated.

Thanks.

Sal

2

You need to groupBy tags in order to get what you want, otherwise the join simply works based on timestamps.