How to return interface alias name rather than name

So I am collecting interface data and what I would like to do is to display the Interface Description(ifAlias) rather than the Interface name. So I’m basically using one measurement as a lookup table. Here are my data structures…

,result,table,_start,_stop,_time,_value,_field,_measurement,host,name,source
,0,2020-12-23T19:48:29.359160489Z,2020-12-23T19:53:29.359160489Z,2020-12-23T19:48:54.200919956Z,45327325934028,in_octets,ifcounters,techops01.ny2,Ethernet24/1,10.85.99.20

#default,last,
,result,table,_start,_stop,_time,_value,_field,_measurement,agent_host,host,ifName
,0,2020-12-23T19:49:12.566793345Z,2020-12-23T19:54:12.566793345Z,2020-12-23T19:49:15Z,HOST=APCON01.NY2 PT=A13 FN=APCON LOC=XXX-XX,ifAlias,Net-System,10.85.99.20,techops01.ny2,Ethernet2/1

name in the first measurement is the same ifName in the 2nd. So I am basically looking to use the second measurement as a lookup table.

I tried to do this…

from(bucket: “systems_telegraf”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ® => r["_measurement"] == “Net-System”)
|> filter(fn: ® => r["_field"] == “ifAlias”)
|> filter(fn: ® => r["_measurement"] == “ifcounters”)
|> filter(fn: ® => r["_field"] == “in_octets”)
|> join(tables: {Net-System: ifAlias, ifcounters: in_octets}, on: [“ifName”, “name”])
|> yield(name: “last”)

And I get this error…

 compilation failed: error at @7:17-7:61: cannot mix implicit and explicit properties

I don’t think a join is necessarily the best choice. But I’m not really sure what the right choice is

Hello @Brian_Gibson,
You’re close.
Since flux is pipe-forwardable, once you filter for one field or measurement then the rest won’t exist.
Instead, try storing the results of each query in a variable before performing a join like:

Net-System: = from(bucket: “systems_telegraf”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ® => r["_measurement"] == “Net-System”)
|> filter(fn: ® => r["_field"] == “ifAlias”)

ifcounters = from(bucket: “systems_telegraf”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ® => r["_measurement"] == “ifcounters”)
|> filter(fn: ® => r["_field"] == “in_octets”)

join(tables: {Net-System: ifAlias, ifcounters: in_octets}, on: [“ifName”, “name”])
|> yield(name: “myjoin”)
1 Like