IoT sensor common queries

• Calculate time in state
• Determine a state within existing values

All scenarios below use the sample dataset provided by the . For more information, see Sample data.

In this scenario, we look at whether a production line is running smoothly (state\=OK) and what percentage of time the production line is running smoothly or not (state\=NOK). If no points are recorded during the interval (state\=NaN), you may opt to retrieve the last state prior to the interval.

To visualize the time in state, see the .

To calculate the percentage of time a machine spends in each state

1. Import the contrib/tomhollingworth/events package.
2. Query the state field.
3. Use events.duration() to return the amount of time (in a specified unit) between each data point, and store the interval in the duration column.
4. Group columns by the status value column (in this case _value), _start, _stop, and other relevant dimensions.
5. Sum the duration column to calculate the total amount of time spent in each state.
6. Pivot the summed durations into the _value column.
7. Use map() to calculate the percentage of time spent in each state.

The query above focuses on a specific time range of state changes reported in the production line.

• range() defines the time range to query.
• filter() defines the field (state) and measurement (machinery) to filter by.
• regroups the data by the field value, so points with OK and NOK field values are grouped into separate tables.
• sum() returns the sum of durations spent in each state.

pivot() creates columns for each unique value in the _value column, and then assigns the associated duration as the column value. The output of the pivot operation is:

Given the output above, map() does the following:

1. Adds the NOK and OK values to calculate totalTime.
2. Divides NOK by totalTime, and then multiplies the quotient by 100.
3. Divides OK by totalTime, and then multiplies the quotient by 100.

This returns:

The result shows that 88.66% of time production is in the OK state, and that 11.34% of time, production is in the NOK state.

Mosaic visualization

The mosaic visualization displays state changes over time. In this example, the mosaic visualization displays different colored tiles based on the state field.

To calculate the time-weighted average of data points, use the .

The example below queries the oil_temp field in the machinery measurement. The timeWeightedAvg() function returns the time-weighted average of oil temperatures based on 5 second intervals.

Output data

Calculate the value between events by getting the average value during a specific time range.

The following scenario queries data starting when four production lines start and end. The following query calculates the average oil temperature for each grinding station during that period.

Output

Use multiple existing values to determine a state. The following example calculates a state based on the difference between the pressure and pressure-target fields in the machine-production sample data. To determine a state by comparing existing fields:

1. Query the fields to compare (in this case, pressure and pressure_target).
2. (Optional) Use aggregateWindow() to window data into time-based windows and apply an aggregate function (like mean()) to return values that represent larger windows of time.
3. Use pivot() to shift field values into columns.
4. Use map() to compare or operate on the different field column values.
5. Use map() to assign a status (in this case, needsMaintenance based on the relationship of the field column values.

Output