adhoc filters

[snatch2013]

Hello,
is it possible to add AdHoc filters similar to InfluxDB plugin?

Regards,
Vladimir

[hagen1778]

Hi @snatch2013
If I understand it correctly, AdHoc filters helps to explore your data. But we are talking about columnar database with huge amount of data and these on-the-fly queries might be quite slow.
And there are more questions:

• from which table we must query tags and values?
• for which period we must query the data (day, month or year)?
• how will we apply these filters if there are graphs with different tables on the page?
• how to prevent unexpected query to the tag with huge number of values (billions)?

For now, I see a lot of problems with it. Could you plz provide us with some scenarios for AdHoc filters?

[snatch2013]

Adhoc filters are applied automatically to all queries that target the given datasource.
For example, I parse network logs and NetFlow. Fields can be src ip, dst ip, src port, dst port, protocol, tcp flags, access-list name, etc. On the dashboard can be many graphs and tables, and with ad-hoc filters for influxdb, it is easy to filter data quickly for all graphs and tables but specifying a value for some of the fields. We just add this value to the top of the dashboard, and it affects everything.

[hagen1778]

But CH is not a key-value storage. You can't just fetch tags and values, as it required by Grafana API, without defining table and interval. If we will try to get uniq IPs from our table, the browser will just hang because of billions of results.
I've checked postgres and mysql datasources, and they doesn't support AdHocs also.

For our datasource it is possible to define regular variables, and make them dependent on each other to achieve desired filtering.

[snatch2013]

Roman,
I don't need the list of unique IPs. I need to add some filtering condition for every database request on the dashboard. So if I add ad-hoc filters protocol='tcp', dst_port=53, it will be added to each database request as where protocol='tcp' and dst_port=53.
Maybe I understand wrong something about grafana, but variables seem not so suitable. For the example above, I have to add the condition AND protocol=$protocol AND dst_port=$dst_port to each request?

[hagen1778]

I need to add some filtering condition for every database request on the dashboard

Grafana requires to implement next funcs for AdHoc:

• getTagKeys(options)
• getTagValues(options)

It means that tags and values must be queried from datasource

I have to add the condition AND protocol=$protocol AND dst_port=$dst_port to each request

Yes

So if I add ad-hoc filters protocol='tcp', dst_port=53, it will be added to each database request as where protocol='tcp' and dst_port=53

But no guarantees that every table will have the same list of columns, like dst_port and protocol.
Also queries might consist of joins and inner requests and we can't just add where protocol='tcp' in the end of query

I see that AdHoc is supported only by ElasticSearch and InfluxDB. I am not familiar with those databases and might mistaken in such questions.

[snatch2013]

They collect tag keys and values just to provide the ability to choose from the list. But, you can also type manually any field name, it shouldn't be the tag name. Also, it is possible to type any value, not only to choose from the list. So in case of clickhouse, it seems like it possible to fetch the list of columns and show this list, but of cause there is no need to fetch all values.

So, according to your answer, it's not possible to use variables to achieve the same goal. Every time you want to change some filtering field, you need to reconfigure all graphs and tables.

Of cause there are cases when you cannot use this filters, but it's the same for influxdb and elastic. But it's ok, nobody make you use this feature if you don't need it.

[hagen1778]

Good point. I can try to fetch all columns and their types from system.columns. And after each change parse all queries into AST and add conditions into WHERE statement at root level. I need more time for this.

Thx for your patience!

[snatch2013]

Thanks, Roman

[ei-grad]

/long negative post deleted/

Never mind. Adhoc filters implementation for initial filtering on the $table could be useful, definitely.

[mattbostock]

We have a use case where we have a drop-down list of ad-hoc filters that we commonly use (e.g. filtering for HTTP request code 200).

Using the SqlDB plugin, it's possible to use ad-hoc filters by adding AND ($filter) to the where clause. The value of $filter is unquoted using the SqlDB plugin, e.g. the query sent to Clickhouse includes:

AND(scheme = 'https') 

This technique does not work with the Vertamedia plugin because it seems that $filter is being quoted:

AND('scheme = \'https\'')

...which prevent this technique from working.

Maybe this could be a simple way to implement this feature?

[ei-grad]

Maybe it would be easier to transform the $table to a subquery with this filters applied? It wouldn't require any AST hustle and $timeFilter should be applied in this subquery (the additional point here - it is now easy to produce the excessive load on Clickhouse server by accidently running several full-range queries if one miss the $timeFilter while writing the query).

[hagen1778]

Hi @mattbostock

Maybe this could be a simple way to implement this feature?

Plugin automatically quotes string arguments in variables. I did it to simplify query building:
WHERE Type = $type - here u don't need to know whether $type variable is integer or string. The same quoting is used for IN arguments - ('type1', 'type2') or (1,2)

[hagen1778]

@snatch2013
I've implemented adhoc filters in #35
Could u plz check whether it's ok for u? U need simply download plugin from that branch and copy into grafana plugin folder to make it work.

Some info about adhocs:

• If there is an adhoc variable, plugin will fetch all columns of all tables of all databases (except system database) as tags. So in dropdown menu u will see options kinda database.table.column
• if there are ENUM columns, plugin will fetch their options and use them as tag values
• plugin will apply adhoc filters to all queries on the dashboard if their settings $database and $table are the same as adhoc's database.table
• there are no option to apply OR operator for multiple adhoc filters - see Support OR operation between Adhoc filters grafana/grafana#10918
• there are no option to use IN operator for adhoc filters due to Grafana limitations

[hagen1778]

@snatch2013 up

[snatch2013]

Roman, sorry for late answer. I will check it today.

[snatch2013]

I have downloaded the branch adhoc-filters, restarted the grafana-server, but wasn't able to make it work.
When I add the variable with type adhoc-filter, I receive a warning that this type of datasource doesn't support adhoc filters. After that, on the dashboard, I can add filters, but they don't have any effect on the output. Also, there is no list of columns to choose from and there is no option to delete the filter (usually it is in the same list with the fields names).
Checked, that new files are included in the plugin:

# ls -a /var/lib/grafana/plugins/vertamedia-clickhouse-datasource/dist/ | grep adhoc
adhoc.d.ts
adhoc.js
adhoc.js.map
adhoc.ts

---

Could you please suggest what to check? Can it be a problem for grafana version 5.0.0? I will check the version 4.6 tomorrow.

Regards,
Vladimir

[hagen1778]

Hi @snatch2013
This is strange, it should work. I've just tested it:

• downloaded latest grafana (brew install grafana)
• downloaded adhoc branch https://github.com/Vertamedia/clickhouse-grafana/tree/adhoc-filters
• copied source code to plugin directory
• started grafana
• configured datasource
• added adhoc filter to dashboards

Probably it's browsers cache? Try to create new dashboard in browser's incognito mode ctrl+shift+n or disable cache in developer tools

[snatch2013]

Yes, sorry, it was my mistake. I missed that the name of the variable should be adhoc. Now it works.
Thanks a lot, this is a very helpful functionality for us.

Regards,
Vladimir

[hagen1778]

Ok, I'll merge it

[hagen1778]

see https://github.com/Vertamedia/clickhouse-grafana/releases/tag/1.4.0