MySQL query to show single/multiple/ALL when using template variables

Grafana MSSQL
1

  • What Grafana version and what operating system are you using?
    Grafana: v9.1.2 (3c13120cde)
    OS: Ubuntu 20.04.5 LTS
    Datasource: MySQL

  • What are you trying to achieve?
    To enable a dashboard operator to use a template variable to enter a single, multiple or ALL values when the number of distinct variable items ~2000.

  • How are you trying to achieve it?
    The template variable contains a list of the distinct values using the following query:
    select name from accounts;
    The panel includes the variable name within its query as follows:
    accounts.name IN (${account:sqlstring}) AND ...

  • What happened?
    There are a few problems:

  1. Because Grafana template variables are limited to just 1000 items, it means that the variable list is always truncated (I actually have about 2000 items that I need to display, but only 1000 are shown)
  2. Because of point.1, if I choose to select ‘ALL’ for the variable, the query seems to use the truncated list into the mysql query. So the mysql query is always missing the other 1000 items in the query
  3. If I try to use a custom ‘ALL’ value (%), I can adjust the mysql query to do something like:
    accounts.name LIKE "$account" AND ...
    And then it works for selecting ALL (and actually gets all including ones that are not in the variable list) but this query does not work if I want to select multiple values from the variable list (an array).
  4. Because of the template variables being limited to just 1000, I’d like to manually type in the values. But it isn’t clear how to type in multiple values that are not listed in the drop down. What is the delimiter between values? (comma/semicolon/space/plus etc dont work)
  • What did you expect to happen?
    Most of my issues would be solved if Grafana could handle the 2000 variable items. As this is apparently not going to be fixed, it would be helpful if I could find a query that allows me to: a) select individual items, multiple items, and all (that actually queries ALL from DB and not just uses the truncated variable list). Also, it would be nice to know how to manually enter more than one item into the variable input

Many thanks for anyone who takes the time to read and reply

2

Welcome

That is quiet a bit of a list to choose from, is is realistic that users will choose from 2k list?
Is it possible you could group these into another sort of category?

For the issue with selecting ALL, this is where a stored procedure comes in handy. When they select ALL, make it so that the SP does a full select with no filter could be my recommendation.

3

Hi, thanks for the reply.

For the 2000 items, unfortunately I cannot see how it can be filtered further as they are account names all within a production environment. The only way I can think possible is if I split it as:

account_A-M
account_N-Z

But this is really clumsy.

With the stored procedure, do you mean kind of like making the mysql query contain an IF statement so if the account= ALL (or whatever custom value I set) it will query all (by referencing a mysql procedure) otherwise it will use the provided list of item(s)? If so I need to brush up on my mysql skills and figure this out :slight_smile:

4

Create an alphabetic and/or numeric filter so that when they click A or 1, only companies with A or 1 come up etc

image

The above is what you can send to your stored procedure, then you check for -1 if so query all acounts.

image

5

Thanks, I will experiment with this.

But one downside I see is if operator wishes to view an account starting with A along side an account starting with Z (so selecting multiple accounts), then I dont think thats possible

6

you don’t think it is possible. But it is :slight_smile:

Try out a few possibilities.

image
image958×441 8.65 KB

Also maybe APache eCharts has some multi selectable alphabets

7

Will definitely experiment and see if I can get what I’m aiming for thanks :slight_smile:

I’ve used eCharts before with a vuejs project and it worked very good. But I was hoping to use grafana for this project without having to build a custom frontend + backend. It would be fun for sure to build it, but would be great if I can get it working the way I want with grafana.

8

Yes eCharts has a grafana plugin is what I meant sorry