[Feature] Parametrized adhoc queries

[bobrik]

Ad-hoc queries cannot have parameters right now (v5.2.1):

We use a Clickhouse datasource with a database that has 14016 fields. That's not usable for autocomplete and it even takes forever to download and parse JSON with all these fields:

• Allow setting database and table for adhoc filters Altinity/clickhouse-grafana#75

To make this workable, we want to have additional settings for adhoc filters, in our case the filter would narrow down fields to a specific table instead of all databases and tables.

[belm0]

Specifically, I'd like to have ad-hoc filters apply to only a limited set of metrics.

For example with Prometheus, we have a device_info metric with labels like "hardware_model", "software_version", etc. These labels do not exist in all metrics, so the ad-hoc filtering as it's currently implemented is not usable. I'd like an ad-hoc filter that applies only to the device_info metric.

[stevenvachon]

This would be useful for the mongodb-datasource and wavefront-datasource enterprise plugins which currently need to rely on additional template variables (with hardcoded names) that act as links to values such as a query or metric name.

@scottlepp @ryantxu @torkelo

[scottlepp]

@belm0 @bobrik One workaround is to create a template variable and reference that from getTagKeys/getTagValues.

[belm0]

One workaround is to create a template variable and reference that from getTagKeys/getTagValues.

I'm not sure how the datasource API is used. Would you give more detail on how that would work?

[scottlepp]

When you create an adHoc filter you will see 2 drop downs added to your dashboard. One for the filter key and one for the filter value. When you select from the filter key, a method on your datasource class, getTagKeys, will be called to get the key values for the dropdown. When you select from the filter value, getTagValues is called.

If you only want keys/values related to device_info, you could create a template variable, something like "metric_link" and set the value to "device_info". Then in getTagKeys, you can reference that variable. Here is an example:

// partial datasource class

import { getTemplateSrv } from '@grafana/runtime';

async getTagKeys() {
    // ad hoc filters require a metric - use a known dashboard variable to define it
    const metric = getTemplateSrv().replace('$metric_link');  // device_info
    // your code here to fetch the keys
    const keys = await this.fetchKeys(metric);
    // must return an array: [{text: 'key'}] ... which is shown in the keys drop down
    return keys.map(k => ({ text: k}));
}

// the key you select is provided in options
async getTagValues(options) {
    // ad hoc filters require a metric - use a known dashboard variable to define it
    const metric = getTemplateSrv().replace('$metric_link');  // device_info
    // your code here to fetch the values for this metric 
    const values = await this.fetchValues(options.key, metric);
   // must return an array: [{text: 'value'}] 
    return values.map(v => ({ text: v}));
}

[belm0]

Thank you for explaining-- I'm not sure how that applies to my use case:

1. I want only keys/values related to device_info in the adhoc dropdown
2. I want the selected key/value only applied to the device_info metric in queries

[scottlepp]

@belm0 create a "constant" template variable and set the value to "device_info". Then reference that variable in getTagKeys/getTagValues. Now you have the metric you want for your query.

[belm0]

When an adhoc template variable is created, every metric of every query in the dashboard gets substituted with it. So if some dashboard query references another metric, say device_temperature, which does not contain the labels of device_info, then the adhoc key/value will be incorrectly substituted into it and the query results will be empty. Am I misunderstanding?

[natellium]

Hey @bobrik thanks for the request! We're now using github discussions to track feature requests and improve how we engage in feature discovery, development and delivery. Therefore I'm closing this issue but if you still want your feature to be considered it'd be great if you could reopen it as a discussion! Here is a bit more info about how to open feature requests as discussions.

[alexforster]

Per #73424 can we have this reopened?