LiveStatusTable
=======================================================
|timestamp |IPAddress |Value |
========================================================
|123456789 |192.168.5.2 |1 |
|123456788 |192.168.5.3 |1 |
=======================================================
Now I need to compare these tables to check which IPAddress are missing in the 2nd table using Flux.
Hi @Jay_Clifford ,
With Join I will get the common values. In my case I need to check which IP address are not in LiveStatusTable but are in the MainTable to determine whihc systems are offline.
Is there a way for me to do this using Flux?
Hi @Aritra666B,
I am wondering if you did an outer join between the two tables. This would then leave null values within rows where there is not a match. You could then use map() to conditional check if the two columns match one another.
Could you export your a sample of your data and I will see if I can build the query for you
You could just use testing.diff(). You should make sure the schema of the two input tables are the same, so if you’re just trying to see what IP Addresses are missing, I’d just drop all columns except IPAddress. You’ll also need to make sure rows are sorted in the same order.
Here’s a working example using the data you provided above:
@scott , @Jay_Clifford I’m trying to do the same thing, but am restricted to InfluxDB 1.8 + Flux 0.65.1 and therefore can’t use the “join” package. I’ve tried the solution using testing.diff and this works great as long as the “missing” values are contiguous in the “complete” table.
If LiveStatusTable looked like this:
I’ve tried to solve this using the getColumn and/or findColumn to create an array of expected values and then use filter to leave only those rows in LiveStatusTable that do not appear in MainTable:
@b_pennies The missing object properties (schema) error indicates you’re trying to use the schema package somewhere in your query. Is this your full query?
The schema package wasn’t introduced until Flux 0.88.0, but it’s essentially a port of the influxdata/influxdb/v1 package, so you should be able to use that instead with the version of Flux you’re using.
@b_pennies Ah, ok, I think I know why. In this particular case, you’re using getColumn() incorrectly. getColumn() actually requires tableFind() to extract a table from a stream of tables. getColumn() then extracts a column from the extracted table and returns an array of column values. So with your current query, getColumn is operating on the stream of tables passed to it from distinct(), not an extracted table.
I’d actually suggest using findColumn() instead of getColumn(). This is a newer implementation that both extracts the table and returns an array of column values. It’s available in InfluxDB 1.8:
Another problem here is that the InfluxDB query API requires that a query returns a stream of tables. Right now, this query doesn’t. You have variables defined, but they query doesn’t call any variable to return it’s value. You could just call data at the end of the query to return the data stream of tables. You wouldn’t be able to call expected because it returns an array. The InfluxDB API doesn’t support returning raw arrays.
where I expect the completed array to be a list of pizza ids that is a superset of those found in the raw unfinished data. The final output is a table (or stream of tables with 1 table?) that only includes those ids that do not appear in the unfinished data.
Interestingly I get a new error when trying to take your advice and using findColumn().
The error is simply undefined identifier "findColumn". From the documentation I’m guessing that findColumn was introduced in v0.68.0 but I only have access up to v0.65.1, which would explain this error.
Yep, you’re right. findColumn() isn’t supported in 1.8 so you’ll have to stick with tableFind() |> getColumn(). Sorry about that. I’m glad the query is working as it should. Let me know if there’s anything else I can help with.
@scott I recently started learning flux and found myself in the same problem. I want to compare data between two tables.
I want to find the number of active imie(which send data in the last 30 min) and the number of inactive imie that did not send any data in the last 40 min. all devices were active 24 hr ago.
a device can send data multiple times in 5 min also imie is a tag.
help me…
The first thing to understand is that InfluxDB can only provide data that does exist. I can’t tell you what data doesn’t exist (inactive imie). To figure this out, you have to query the list of all imie’s within the past 24 hours. To do this, you can just query all data from the last day, downsample the data to make it a little more manageable, ungroup it into a single table, and return all the unique values of imie:
@scott don’t know why getting this error unique: schema collision detected: column "_value" is both of type float and string when running the above query. I’m sending imie number as string but still getting this error. example imie: 12AC1243224SD12
Ok, that error is being caused by ungrouping the tables (group()). This combines all rows into a single table, but all values in a column must be of the same type. This means you have fields of different types. I think the best way around this would be to query specific a field by adding filter() after both range calls: