We are testing satellites and the telemetry (during testing on ground) is injected into a mysql database. I have 10k+ different parameters in this database. The user (test engineer) wants to be able to select specific parameters for an ad hoc plot. Always time-series. So predefined dashboards are not very useful to me, as the user will want to change the plots for every session. The mysql code is quite specific and could be a hurdle for the user to each time create the mysql code for each parameter. Is there a way in grafana to create a list of available parameters, each with a predefined assigned mysql code. And then allow the user to select the parameter which he/she want from a list?
If this would be possible, can I generate import the mysql query code for each parameter in some way through a script?
@yosiasz, thanks! I feel very privileged to be working with cool projects.
But you would be suprised about the older technology which is used to test and operate satellites.
Anyway. The mysql is quite straightforward. I have about 10.000 parameters, for each, I can easily generate the mysql query, which will always be like this: SELECT gentime/1000000 as "time", value as "amount" FROM t_par_dbl where param_id = 36947
Where t_par_dbl and 36947 will be varying for the different parameters. So I need 10.000 individual definitions. Ideally, I can put these in a script or file and import it.
To select the parameters would be nice, like the printscreen in example above. With this amount of parameters, it would however be required to be able to search this list in some way (or have a dropdown). As a bulletlist of 10k entries will not be user friendly. Thanks for your advice!
The structure is as follows. We have about 10k parameters. Each parameter has a description and an ID. I would like to put the description in the item list. The query then uses the ID. 36947 in above example. There are about 5 tables in which the values of the parameters are stored. This depends on wether the parameter is a uint, dbl, etc… I can quite easily make the queries for each parameter. In another thread it proved to not be possible to lookup which table is used for each parameter (this table is stored in another table). I tried combining all tables, but this also does not work, as some parameters are in 2 different tables (1 for the uncalibrated value and 1 for the calibrated value). Could you show me how to create the filtering list from your example? And how I could define the items in this filter list + load queries for each parameter?
I’m wondering if the search from variables will be enough - maybe you could create a Table visualisation with all the possible values / combinations and use data links to populate a text box variable? (Basically the table would serve as the variable picker.) I think Table visualization would be more readable (you can make it bigger) and filter functionality on columns might come in handy. With Infinity Plugin you could also create it from some rest API, so you wouldn’t have to regenerate the dashboard every time a new parameter / table appears?
@jangaraj
The database is not under my control to change. It is not ideal…
I have a first table t_par_ref, which has a field “name”. This table also has the field “param_id”. I need this “param_id” for my query. In normal mysql I can lookup the param_id based on the parameter name using: SELECT param_id FROM t_par_ref WHERE name = "OPPSM001"
This same table also has a field t_raw, which contains the name of table in which I can find the value of the parameter (example t_par_dbl, t_par_u16, t_par_u32, …). I can lookup which table I should use using SELECT t_raw FROM t_par_ref WHERE name = "OPPSM001"
With these 2, I can query the table containing my values. I wanted to try the following:
SELECT gentime/1000000 as "time", value as "amount"
FROM (SELECT t_raw FROM t_par_ref WHERE name = "OPPSM001")
WHERE param_id = (SELECT param_id FROM t_par_ref WHERE name = "OPPSM001");
The last line works fine. But in the FROM line, I get an error. I understand grafana does not allow dynamic references to a table. I tried using a grafana variable including all possible table (t_par_dbl, t_par_u16, …), using the following code:
SELECT gentime/1000000 as "time", value as "amount"
FROM ${available_tables}
WHERE param_id = (SELECT param_id FROM t_par_ref WHERE name = "OPPSM001");
This works quite well, but a single dashboard contains a combination of parameters of type u16 and double (as example), which then only return data in case the dashboard variable is set to one or the other table. I do not seem to be able to select a table per parameter.
So I would like to have a selection list, like @yosiasz proposes.
Ideally with the following text:
OPPSM001 - Battery voltage
OPPSM002 - Battery current
OPPSM003 - Solar array current section 1
OPPSM003 - Solar array current section 2
…
If the operator selects one of more parameters, they are shown in a time-series. If possible, I would hard-code which table to be used for each parameter. Otherwise the test operator need to look for each parameter in which table the value is stored. I’ll get to work with @yosiasz reply, but if you would have any further help, always welcome!
SELECT
$__timeGroupAlias(gentime/1000000, '5m'),
AVG(value) AS "value"
FROM ${table:raw}
WHERE
param_id = ${parameter:value}
-- SOME time filter condition is needed here, e.g. "AND $__unixEpochFilter(gentime/1000000)"
GROUP BY 1
ORDER BY 1
All SQLs are just examples. Test&fix any syntax/functional issues for your DB structure. Use the query inspector to see what kind of SQL is generated. The challenge is to design a user-friendly UX without reaching any limits. Implement a minimal working example first and then add more features (e.g., multi-select of variables, search, aggregations, grouping, etc.), which will add more complexity.
