Assign one variable to another

Grafana
1

I am using mysql. The task is as follows. I have a “country name” variable. In mysql code, I want to insert not the name of the country, but the code.

SELECT concat(name, " ", code) FROM BillingCountry

download
download742×178 27.1 KB

But … there is a feature. When I select a variable, I want to see the name of the country (or a name-code pair), and only the country code is substituted in mysql

2

I got answer bymyself
link to doc of Grafana

Another option is a query that can create a key/value variable. The query should return two columns that are named __text and __value . The __text column value should be unique (if it is not unique then the first value is used). The options in the dropdown will have a text and value that allows you to have a friendly name as text and an id as the value. An example query with hostname as the text and id as the value:

SELECT hostname AS __text, id AS __value FROM my_host

at my case I use

SELECT name AS __text, code AS __value FROM billingCountry

Screenshot_1

but at sql script I use the BillingCountry.code

IF ('ALL' in ($countryOfUser), TRUE, BillingCountry.code in ($countryOfUser))
1 Like
3

Can you share 3 to 4 rows of your data set and the query which you used and results for this query . I have a similar problem.

4

well, I have table BillingCountry and Users
страны

at Grafana I create Grafana’s variable $countryOfUser which have list of countries by name of countries and code of countries.

In order to get a list of mapped values, I use a query with 2 entities:

SELECT name AS __text, code AS __value FROM billingCountry

you can see at screenshoot

grafana -screensht
grafana -screensht930×429 67.7 KB

And then dashboard users can use the filter on a graph or table where they see the names of countries.
And in sql query, I use country codes, since they are indexed for me
counties

and I use code for got only from needed countries using by index of countries

    SELECT
      UNIX_TIMESTAMP(MAKEDATE(YEAR(registered), DAYOFYEAR(registered))) as time_sec,
     COUNT(User.id) as value,
       CONCAT("New Registrations ", IF ( $countryOfUser ='ALL', '',  $countryOfUser)) as metric
   FROM User
    LEFT JOIN Country On Country.code = User.countryCode
    WHERE $__timeFilter(User.registered) AND IF('ALL' in ($countryOfUser), TRUE, (Country.code in ($countryOfUser)))  
    GROUP BY DATE_FORMAT(registered,"%Y-%m-%d")

As a result, I got a quick query, since I use indexed fields

is it enough? Or you need some more details?