Configuring Grafana Dashboard with InfluxDB Data




Another great option is to add the InfluxDB that you have created previously as a Data Source. The following explains how to create a dashboard that displays how long the lathe has been operated.

Add InfluxDB to Grafana:

  • In the left menu, click on the Gear icon to open Data Sources.

  • Click on Add data source.

  • Select InfluxDB.

  • Replace InfluxQL with Flux in the dropdown menu called Query Language.

  • Type http://influxdb:8086/ at the URL field in the section called HTTP.

  • In our example, we write purdue_gatewaylabs into the Organization field in the InfluxDB Details section.

  • Copy your Influx API Access Token in the field. (Once the save and test button is clicked, the password is hidden and replaced with configured.) We decided to add the Default Bucket gateway_bucket.

  • Save & Test: Success will display a green notification (3 buckets found). Please see below.

Creating a Stat Visualization in Grafana to Show Spindle Runtime

To create a stat visualization in Grafana that shows the spindle runtime of the past 10 minutes, follow these steps. Note that 10 minutes and seconds as units were chosen for demonstration purposes; feel free to change the time range and units to fit your needs.

Step 1. Set up Your InfluxDB Query

  • Open Grafana and navigate to the dashboard where you want to add the visualization.

  • Click on "Add Panel" and select "Add Query".

  • Select your InfluxDB datasource.

Step 2. Write the InfluxDB Flux Query 


// Step 1: filter data for spindle speed
data = from(bucket: "gateway_bucket")
  |> range(start: -10m)  // Adjust this to your desired time range
  |> filter(fn: (r) => r._measurement == "Acra/ATL-618EVS_Test" and r.topic == "Telemetry/Spindle" and r._field == "spindle_speed_RPM")
  
// Step 2: Aggregate data to 1-second intervals to ensure regular intervals
aggregated_data = data
  |> aggregateWindow(every: 1s, fn: mean, createEmpty: true)  // Include empty intervals to see gaps
  
//Step 3: Fill null values only when the spindle speed should be non-zero
filled_data = aggregated_data
  |> fill(column: "_value", usePrevious: true)
  |> map(fn: (r) => ({
      _time: r._time,
      _value: if exists r._value and float(v: r._value) == 0.0 then 0.0 else float(v: r._value),
      _field: r._field,
      _measurement: r._measurement,
      clientID: r.clientID,
      topic: r.topic
  }))
  
// Step 4: Filter out the periods when the spindle is stopped (speed is 0)
running_data = filled_data
  |> filter(fn: (r) => r._value > 0)
  
// Step 5: Calculate the total runtime in seconds
total_run_time = runing_data
  |> count(column: "_value")
  |> map(fn: (r) => ({ r with run_time_seconds: r._value }))
   
// Step 6: Yield the final result
total_run_time
  |> yield(name: "run_time_seconds")

Explanation of the Query:

1. Filtering Data:

  • The from() function selects the data from the specified bucket.

  • The range() function filters the data to the last 10 minutes.

  • The filter() function further narrows down the data to the specified measurement, topic, and field where the spindle speed is recorded.

2. Aggregating Data:

  • The aggregateWindow() function aggregates the data into 1-second intervals. This ensures regular intervals in the data and includes empty intervals to detect gaps.

3. Filling Null Values:

  • The fill() function fills any null values using the previous value to ensure continuity.

  • the map() function further refines thye data, setting spindle speed to 0 if it should be non-zero but is missing, and converting values to float for consistency.

4. Filtering Runing Periods:

  • The filter() function removes periods where the spindle speed is 0, retaining only the periods when the spindle is running.

5. Calculating Runtime:

  • The count() function counts the number of 1-second intervals where the spindle was running, effectively giving the runtime in seconds.

  • The map() function labels the counted value as run_time_seconds.

6. Yielding the Result:

  • The yield() function outputs the final result, labeled as runtime_seconds.

Step 3: Configure the Query In Grafana

  • Paste the Flux query into the query editor in Grafana.

  • Test the query to ensure it returns the expected results. You should see a single value representing the total runtime in seconds for the past 10 minutes.

Step 4: Configure the Stat Visualization

  • Click on "Visualization"* in the right-side panel.

  • Select "Stat" as the visualization type.

  • Configure the display options:

    • Title: Set a title for your visualization, e.g., "Spindle Runtime (Past 10 Minutes)".

    • Value Options:

      • Unit: Select "seconds" to display the value in seconds.

      • Decimals: Set the number of decimal places to display.

    • Graph Mode: You might want to disable the graph mode if you only want to display the stat number.

    • Color Thresholds: Optionally, set thresholds to change the color based on the runtime (e.g., green for below 100 seconds, yellow for 101-200 seconds, red for above 201 seconds).

      The value mapping menu with 3 ranges configured, 0 to 100 set to green, 101 to 200 set to yellow, and 201 to 600 set to red.

Step 5: Save and View the Panel

  • Save the panel and your dashboard.

  • View the panel to ensure it displays the spindle runtime for the past 10 minutes correctly.

Additional Note

To add an input field for the user to select the start and stop times using a calendar in Grafana, you can utilize Grafana's built-in time range controls. This allows you to dynamically set the time range for your query without hardcoding it in the query itself.

Step-by-Step Guide

Step 1: Modify the Flux Query to Use Variables

Update your Flux query to use the variables for the start and stop times. Grafana uses v.timeRangeStart and v.timeRangeStop variables to represent the selected time range.

Replace the following line in the flux query above, which is under Step 1:


  |> range(start: -10m) // Adjust this to your desired time range

with:


  |> range(start: v.timeRangeStart, stop: v.timeRangeStop) // Use Grafana variables for time range

Step 2: Configure the Time Range

  • In the top-right corner of the Grafana interface, you'll see a time range selector. This allows you to select the start and stop times using a calendar.

  • Set the desired time range using this selector. The selected time range will automatically be passed to the query using the v.timeRangeStart and v.timeRangeStop variables.

Step 3: Save and View the Panel

  • Save the panel and your dashboard.

  • View the panel and ensure it dynamically updates the spindle runtime based on the selected time range.

Summary

The process involves creating a Flux query to calculate the spindle runtime over the past 10 minutes and configuring a stat visualization in Grafana to display the result. By following these steps, you can monitor the spindle runtime effectively and ensure maintenance schedules are met.