FFT plot: plot arrays in JSON

[dsxoiniana]

Hi all,

I use Elasticsearch and Filebeat to fetch data from 2 Node MCUs via the UDP interface. The messages are formatted within the MCUs and are sent successfully in JSON format:

"message": {
      "FFT_Freq": [
        0,
        78.125,
        156.25,
        234.375,
        312.5,
        390.625,
        468.75,
        546.875,
      ],
      "FFT_Magn": [
        3394.808,
        1488.456,
        15.87906,
        54.76368,
        17.0278,
        86.5359,
        133.0211,
        81.6696,
      ],
      "cnt_Tx": 6565,
      "deviceID": "CurSensor_MCU",
      "t_Tx": 2144553233,
      "MCU_current": 101.1
    }

Grafana is accessing the data just fine. However, the FFT_Freq and FFT_magn fields contain a signal’s FFT Frequencies and respective Magnitudes as arrays in the JSON. Of course FFT is not “time-variant” to display it as time-series, but it would be nice if I could visualize a kind of X-Y plot, with X being the Frequencies and Y the respective magnitudes, and these would be refreshed each time a new message arrives in Grafana.

Is there a way I can achieve that in Grafana? I’m aware of the posts here and here, but they are not of much help.

[zuim]

I think the ‘Plotly’ Plugin does exactly what you want. I have tested to create a graph with it, which has time independent X and Y axis:

I don’t know elasticsearch, but you just have to write your query so that you get one column with frequencies and one column with magnitude for the selected time range. Just use something like last() to only show the most recent analysis.

[dsxoiniana]

Hi zuim, thanks for the reply. I’ve checked plotly, but it seems my problem is one step before, i.e., the values I get for the frequencies and magnitudes are not the complete arrays but only the first element of the array. So, in essence I need to parse somehow those arrays which I don’t know how to do.

[zuim]

It’s hard to help with that, because I don’t know elasticsearch. I recommend you to start by removing all filtering by time from your query. Then only add one column with a timestamp. Then you should filter by either last FFT data or some other metric.
Maybe it would help, if you show your current query.
In case it helps, this is how I would do it using mysql:
SELECT fft_freq as xAxis, fft_magn as yAxis, timestamp as time FROM fftdata WHERE fft_id=$fft_display_id

[dsxoiniana]

I had a bit of a progress by switching from Elasticsearch to InfluxDB. I use Telegraf to parse the JSON messages and I forward them to InfluxDB. In Grafana I now get separate columns for each JSON fields and the arrays are split as well! So I can now select FFT_Freq_0, FFT_Freq_1, FFT_Magn_0,…etc. I now miss how the query should look like, so that I select each of the FFT_Freq_* to be plotted against FFT_Magn_*. Any ideas?

[zuim]

Can you post your current query?
Does it work, if you manually make a query like
SELECT FFT_Freq_0, FFT_Magn_0 FROM fft ? This should create a table like:

TIME(ignore) | FFT_Freq_0 |FFT_Magn_0
             | 10Hz       | 1
             | 50Hz       | 20
...

Which should be possible to plot.
then you could either replace the number with a variable if you have a small amount of data or maybe change your data structure so you have a field for the id instead and use WHERE fft_id=$var

[amotl]

Dear Dimitris,

you might want to have a look at what we are doing over at Hiveeyes.
TLDR; We are using InfluxDB and Grafana Heatmap.

How to Visualize FFT Data in Grafana

Erste Analyse der Audio-Daten des BeABee-Projekts

With kind regards,
Andreas.

P.S.: After posting this, I recognized that I actually answered on FFT PLOT (fast fourier transformed) on grafana - influxdB already referencing the same links.

[dsxoiniana]

Indeed, I run the query SELECT "FFT_Magn_0", "FFT_Freq_0" FROM "meas_ext" WHERE ("deviceID" = 'CurSensor_MCU') AND $timeFilter and in plotly I can plot a X-Y diagram. I can add one query in Grafana per each Freq-Magn pair and plot them, but this is not practical and takes a lot of time to configure. The data is there in a table like you posted, so I just need to ask the DB to fetch all FFT_Magn_i as yAxis and FFT_Freq_j as xAxis, i,j in [0,63]. I’m not familiad with DBs, any hint on how to insert variables in my query would be much appreciated

[dsxoiniana]

that’s a nice effort, but in the forum I can’t figure out the settings to plot those diagrams. Any hint on how to adapt my data for Heatmap?

[zuim]

This is the documentation for the grafana variables I’m talking about: https://grafana.com/docs/grafana/latest/variables/templates-and-variables/
Basically you replace the number with a variable, which can then be selected in grafana.

First add a variable in the dashboard settings with name plotID, type query and add a query which returns a list of ids.
Something like this should work: SHOW TAG KEYS from meas_ext
Then add a regex to filter out the ids:
/ FFT_Magn_(\d+)/
Then enable multi value if you want to show multiple plots at once.

Then use the variable in your query
SELECT "FFT_Magn_[[plotID]]", "FFT_Freq_[[plotID]]" FROM "meas_ext" WHERE ("deviceID" = 'CurSensor_MCU') AND $timeFilter

You can also turn on Repeat Options to allow showing multiple Plotly graphs at once.

[dsxoiniana]

What works for me for the variable is the query show field keys from meas_ext. Then with the regex I get the IDs from 0-63. However, when going to the dashboard, I use your query and I select all IDs in the dropdown menu, however only the first entry is plotted (FFT_Magn_0, FFT_Freq_0). Perhaps something is missing from the query?

[zuim]

Does it show other Plots than the first, if you only select one id? Then you just have to enable the “Repeat Option” in the panel settings. If that is not enabled it will replace the variable with a comma separated list of all selected values, which will not work with you current query.

[dsxoiniana]

@zuim

SELECT “FFT_Magn_[[plotID]]”, “FFT_Freq_[[plotID]]” FROM “meas_ext” WHERE (“deviceID” = ‘CurSensor_MCU’) AND $timeFilter

If I select one id it shows only the corresponding pair of FFT_Magn_id and FFT_Freq_id. Repeating is enabled (tried both Horizontal and Vertical but no change).

[zuim]

Have you selected the variable to repeat like this?
(Interface is the variable name)
And you have to click out of the variable selection menu for it to update. Also do no use the “all” variable option, use multi checkbox instead.

[dsxoiniana]

Yes, I select plotID in the Repeating option and I tried also with Horizontal and Vertical but to no avail.

[zuim]

I don’t know why it’s not working then. Maybe someone else can help

[dsxoiniana]

I appreciate your help, I learned quite some stuff. Isee in your screenshots a different interface than mine. What version of grafana are you using? Also, I thought maybe I’m missing a kind of GROUP BY clause?
EDIT: I installed latest 7.0.0 beta3 version. When checking the specific panel I still see one pair of values being plotted, but when I switch back to the dashboard, I now see many separate diagrams for all pairs plotted one below the other as separate graphs. As I select different IDs, new panels are added to my dashboard, but I would expect I would plot them all together in one panel.

[zuim]

If you want to have multiple graphs in one panel you have to disable the repeating option in the panel settings. Then the variable will contain a comma separated list of the selected values.

Then you have to modify your query so that it returns data for all of the selected options, although I’m not sure how to do this in this case and if plotly can automatically show all plots. If all plots use the same frequencies it will be sufficient to just get the frequencies once and only the measurements for each plot.

The group by statement is used to combine multiple values into one. Normally you would group by time interval to get the average of each e.g. 5minute interval, but I don’t think you need it in this case, because your data is not directly time dependent.

PS: I’m not sure if this would work, but maybe you could modify the variable so that it contains the entire column names like FFT_Freq_3 instead of only the number and then use it directly in the query as column names like this SELECT “FFT_Magn_0”, [[plotID]] FROM …

[dsxoiniana]

I wasn’t able to progress with that. The closest I’ve reached to get a meaningful diagram is via the “manual” query:

SELECT "FFT_Magn_0", "FFT_Magn_1", "FFT_Magn_2", "FFT_Magn_3", "FFT_Magn_4", "FFT_Magn_5", "FFT_Magn_6", "FFT_Magn_7", "FFT_Magn_8", "FFT_Magn_9", "FFT_Magn_10", "FFT_Magn_11", "FFT_Magn_12", "FFT_Magn_13", "FFT_Magn_14", "FFT_Magn_15", "FFT_Magn_16", "FFT_Magn_17", "FFT_Magn_18", "FFT_Magn_19", "FFT_Magn_20", "FFT_Magn_21", "FFT_Magn_22", "FFT_Magn_23", "FFT_Magn_24", "FFT_Magn_25", "FFT_Magn_26", "FFT_Magn_27", "FFT_Magn_28", "FFT_Magn_29", "FFT_Magn_30", "FFT_Magn_31" FROM "meas_ext" WHERE ("deviceID" = 'CurSensor_MCU') AND $timeFilter

which gave me a heatmap like this

In principle it’s close to what I’m trying to achieve. I like to maintain the time axis because it shows nicely how the FFT amplitudes change in time. Then on the Y-axis I have the log10 representation of the amplitudes (actually not exactly the amplitudes, I think heatmap shows a COUNT of the amplitude values that fall within each bucket). What is missing from this picture is the frequencies. The diagram is read also in a horizontal manner, that is for example the top 2 blue lines correspond to frequencies 0 Hz and 32 Hz (I sample a DC signal mainly with some noise). Then the yellowish areas correspond to higher frequencies and they correspond to the noise. However this diagram does not display any frequency information and this is what I want to achieve somehow.

The other thing that is probably redundant in my setup are the frequencies values. They remain constant, so perhaps it’s not required to send them from the Arduino because right now the query:

SELECT "FFT_Freq_0", "FFT_Freq_1", "FFT_Freq_2", "FFT_Freq_3", "FFT_Magn_0", "FFT_Magn_1", "FFT_Magn_2", "FFT_Magn_3" FROM "meas_ext" WHERE $timeFilter LIMIT 10

returns

which is a bit stupid to send the same frequency values in each row. I need somehow to relate each frequency to the respective magnitude values and still maintain the time information.

[zuim]

I just tried a few things and I think you are right and the default mode is to display the count of values. You could try the time series buckets mode in the axis settings.
It should display one field for each frequency with the color showing the magnitude. The only problem would be that it uses the column names for the y axis. You could use “as” to rename the columns in your query, although I don’t think that could be done automatically.
Perhaps you could change you data recording code to replace FFT_MAGN_1 with something like FFT_62_5. That would show time on x, frequency on y and magnitude in the color.

An alternative could be the plotly panel, which allows you to create custom traces with custom x and y values. So you could select xaxis: time, yaxis: frequency and metric: magnitude. It only displays points, but I think with many points it would look similar. For this you would have to modify your data to this format:

TIME | frequency | magnitude
1        1              10
1        2              5
1        3              10
2        1  ....

This would also make the query much shorter.