Hello,
I hit the following problem and feel stuck, so thanks for the help in advance.
I am indexing documents with 5 fields: id, type, country, status and timestamp.
Using graph panel I want to show the count of processed documents (status = “PROCESSED”) within the time interval. I also want to be able to filter against country and type.
I declared the variables: type and country as query type.
The problem is that country can come as null and filters are implicitly ANDed.
If I select All (default value) for “country” grafana ORs the found terms behind the scene during query construction, e.g country:FRA OR country:UK
but this way the query doesn’t take into account the documents without country.
It would seem more logical to me to treat All as ( to remove the filter completely from the generated query) but it seems that it’s not the case.
Any idea how to achieve what I want?
Thanks again,
Milan
you will have to write that condition yourself Grafana does not know that this is what YOU mean by all. All could just mean exactly what your variable query returns.
Thanks for the reply.
You mean to write it inside query definition?
I tried, but no luck. Will try further, but if you have an idea, please tell me.
But anyway (IMO) how you translate currently ALL as country: (FRA OR UK OR USA) can be changed to
"_exists_:country"
which is the ES extension of lucene query syntax.
Also, it would be good to have option like ANY which would effectively mean IGNORE country filter.
Currently it can be done by removing variable from dashboard settings and changing query definition, which is really not suitable for business user.
Thanks for the suggestion.
It doesn’t seem to do what I need as per ES documentation.
Pure wildcards \* are rewritten to exists queries for efficiency. As a consequence, the wildcard "field:*" would match documents with an empty value like the following: { "field": "" } … and would not match if the field is missing or set with an explicit null value like the following: { "field": null }
Wildcards are rewritten to exists query as already mentioned in this thread.
I tried more and this is the query that works when the passed value is ALL
type: [[type]] AND ((NOT(_exists_:country)) OR country:[country])"
since it returns the documents irrespective of the country field, which is exactly what I want.
but obviously doesn’t work properly when the passed value is FRA, since it would return documents with country=FRA and without country.
Tried, parsing exception in ES. Could not find the allowed format of the All custom value in the docs.
And if it worked, any new country in the source data would be a maintenance problem, since All needs to be updated each time.
I don’t think this is viable option. I have 50+ countries, more to come at the source and I don’t have control when they are added. Declaring them with OR inside All custom variable is something I cannot do, since it requires constant maintenance. I am still looking here for the option in grafana that effectively removes particular filter in generated query. @torkel , what do you think?
IMHO better option is to change your data. I have had the similar problem with Postgresql. I just don’t use NULL, but string <NULL> and my Grafana life is better :-).
You may fix your current filter problem, but then you may discover that Grafana has another problem with graphing of null values. Try to group by countries, so one country will be null and check if you can visualize it in the Grafana.
I get your point, this is however the case when you control the indexing side, which may or not be true.
Now to your other point:
If country is missing in some documents, you can still aggregate, no problem. ES query (with grafana flavour would be: GET /documents/_search { "size": 0, "query": { "bool": { "must": [ { "match": { "status": "PROCESSED" } }, { "query_string": { "query": "type:MS_WORD AND (_exists_:country OR _missing_:country)" } } ] } }, "aggs": { "by_country": { "terms": { "field": "country" } } } }
Note the way how all the countries (existing and missing ones) are included in the filter (using special ES extension for lucene query syntax)
I’d love if I could specify _exists_:country OR _missing_:country for All (custom option) in grafana, but this is not possible since All is rendered inside [country] country:[country]
Sure, you can write a query and TSDB (ES) will return a result, where one value will be NULL (empty string/nil/…). Will be Grafana be able to visualize it (for example in the graph panel)?
My (postgresql) experience is saying that some null/empty string in the result and Grafana won’t be able to create a graph. Test it in advance - it can be your showstopper or maybe you don’t have a use case, where null will be returned.
I have missing countries in my documents index, but the query I posted above will only return aggregated values for documents WITH country field set, the bucket with NULL (non existing values) won’t be returned, which is what I would expect. So this can’t break grafana panel.
Raw editor feature is really nice, but the feature request is 3 years old, cannot have really high hopes.
