Complex Data Transformation (like parameter fitting) as external script

Grafana
,
1

Hello Grafana Community,

I have been building applications with grafana for some time now. It was working fine (after finding the right key words for google), hence my only recent registration here.

However, I stumbled upon a more complex data transformation that I can not implement in grafana and found no solution using google:
The data I need from the data base is aggregated as I need for the job, but the next step is a (numerical) parameter optimisation (“fit”) to obtain the result value that I want to display.
I do not expect to have such a feature included in grafana, but it gives an example for the use case of further data processing in an external script.

Now, my question is:
Is there a way to pass the current data for a panel to a script (python, bash,…) transform it and pass it back to the grafana panel as a transformation (like add a calculated column)?
Or does this have to be implemented as a custom data source?

Thanks in advance!

(I am using the Open Source version with an SQLite data source, it this makes a difference. But I can imagine this problem to be a general one)

2

Probably not. You can imagine what kind of yuge security hole this could create for someone to take advantage of.

What exactly are attempting to do with this transformation?

3

By external, I referred to the same machine, just not part of grafana but of the OS it is running on. That this structure can be a significant security risk for a system is clear to me.

The application I have is calculating light temperature from RGB information via the black body spectrum. If the resulting formula was “easily” solvable for the temperature, I would have done it in the SQL query, but in this case it is too complicated.

4

Hi @rs232 (by the way, awesome username…wish I would have thought have it!)

Although I have not used it, this might be worth looking into. It would mean moving from SQLite to Influx 3.0, but both use SQL and it’s free to sign up for Influx 3.0 cloud (the OSS / on prem version is “coming soon”).

5

Please provide some sample data as inline csv including column data types and the expected result.

Col1 decimale(10,3)
Col2 float
Col3 varchsr(50)
Col1,col2,col3
33.5,12.3,rgb(54,55,55,

If on ms sql server you can make use of ML services to make python script calls

EXEC sp_execute_external_script
@language = N'Python'
,@script = @PythonScript
,@input_data_1 = @SQL
,@params = N'@ExcelFilePath NVARCHAR(MAX), @TableName NVARCHAR(200)'
,@ExcelFilePath = @ExportPath -- file path where Excel files are placed
,@TableName = @TableName

Type of thing

6

One set of numbers is:

Red, Green, Blue, Temperature (in K)
114, 150,224,5085

With wavelengths of 640nm, 524nm and 470nm, the temperature of an equivalent black body radiator can be derived from Planck’s law (one free parameter - temperature - from the equation plus the scaling to the data points). For this case it is about 5085K. Below is a plot of the fitted function to the data points:
image

7

Also what db type are you on? Mysql, postgres or ms sql?

8

I am on an SQLite3 database at the moment. I took it for simplicity, access rights management from the operating system is fine for my application (one writing entity, all others with read access).

I am digging into the influxDB documentation as suggested by @grant2, “flux” seems to be able to do the job (request.do() function). Transitioning would also be rather simple, as influxDB can take data input from SQL DBs.

1 Like
9

i would go with what @grant2 proposes though it could be done against your sqlite db also. Maybe show your data in grafana as basic table then ability to select a few rows then click a button that will prepare the data for a python script to process

image
image868×709 37.7 KB

10

For the previous plot I used the following formula I found online. Can you verify if it is accurate

h = 6.626e-34
c = 3.0e+8
k = 1.38e-23

def planck(wav, T):
    a = 2.0*h*c**2
    b = h*c/(wav*k*T)
    intensity = a/ ( (wav**5) * (np.exp(b) - 1.0) )
    return intensity
11

Yes, the formula is accurate.
However, my goal is not to draw these curves, but to derive the temperature from the RGB color values, i.e. the value for T best matching with the R,G,B values for Plank’s law.

In the data, I have many sets of RGB values and the walk of T is to be plotted - which is trivial as soon as it is calculated.

Another option I found is the “infinity” data source: It requests a file from a web server which can be built with a php server and receives the required parameters via the URL from grafana. This approach is simpler than querying the data with grafana and then transferring it back and forth to transform it.

1 Like
12

That sounds doable. max 4 parameters? R G B and T?

There are few ways you could skin this cat. I started this one but got distracted. As you can see you could write your stuff in js/ts and then reference it in this Dynamic Text plugin.

image
image752×633 39.9 KB

or data manipulation plugin

image
image1050×779 47 KB

13

I managed to get the data into grafana as intended. My solution is the following:

I am using a php server and the infinity data source.
grafana passes the dashboard parameters to the php page which calls the database instead of doing it directly in grafana.
The returned data (one set of parameters per line) is passed to gnuplot for parameter fitting. But any tool can be used here that is accessible from the php server.
The result is formatted as csv on the resulting webpage that is then loaded by grafana.
Depending on the amount of data, complexity of the calculations and CPU power of the server, it can be necessary to extend the timeout for the infinity data source.

This results in a curve lining up with the original colour data (see figure below for some sample data): In the upper plot, the brightness measurements (as pulses per time) are shown for a sensor without filter as well as red, green and blue colour filters.
These values are used to calculate the colour temperature in the lower plot. The uncertainty from the fit is shown as error band around the data points.

Translation for the German labels:

  • “Helligkeit” → “brightness”
  • “Farbübergang” → colour transition
  • “Farb-Temperatur” → colour temperature
  • “ohne Filter” → without filter
  • “rot/grün/blau” → red/green/blue

image
image947×755 62.4 KB