Filtering option for Elasticsearch datasource

Elastic Stack Beats
1

Hi Team,
For metricbeat agent, a new field is added via metricbeat.yml with below config

fields:
application: [“exxS-e11”,“eBxxxxH-e11”,“exxS-e10”]
fields_under_root: true

So in kibana, the app info is displayed as below

image
image759×67 5.62 KB

Then created a dashboard in grafana with Elasticsearch as datasource and added a variable to list applications for filtering,
image

Clarification:
while we filter any one application from the list to get unique count, by default includes other 2 apps count and display value as “3”. This may be due to cardinality aggregation feature in ES, But we wanted to filter and display value as “1”. is it feasible?

image
image856×519 21.2 KB

2

Hi @karthick2020 and welcome to discuss :slight_smile:

The cardinality aggregation counts the number of different values in a field, and in your example a single document with three values in a field would count as cardinality 3.

One thing you can do with the cardinality aggregation is to use script, that allows to compute on query time a different value for the cardinality count. This comes with a significative performance hit, but would be more flexible for your case. Not sure though if this is supported by Grafana.

Another thing you can try is, depending on the nature of these labels, to store a precalculated "id" field with something like the concatenated strings, something like that:

fields:
    application: ["exxS-e11","eBxxxxH-e11","exxS-e10"]
    application_id: "exxS-e11:eBxxxxH-e11:exxS-e10”
fields_under_root: true

And then calculate the cardinality of the "id" value.

But this won't work as intended if you cannot guarantee the same "id" for the same combination of values.

3

Hi @jsoriano, Thanks for the update. Regarding your 2nd option to use precalculated "id" field. Could you please share more details with example query to understand how this is interlinked to application (array of values) while intended to calculate distinct count.

4

It would be the same query as you are using now for application, but with application_id.

5

we have created multiple custom fields and enabled filter option for end user to query one value at a time from array of values. But with above suggested format, to maintain as single string would be complex when we have more application count.

So can we use any custom calculation during the capture or query, to understand that this is a single application selected rather than 3 entries altogether for Application field?

Also can we use nested object type? so each object can be queried independently from other objects.

6

Yes, you can use a custom calculation on query time using the script option. This calculation could be used to create a unique value from the multiple entries that you may have.

How would you structure your data with nested objects?

I don't think that nested objects could help here. They would have some advantages if you need to make queries on documents that have arrays of more complex objects. But in this case with arrays of strings they wouldn't provide any advantage. Also take into account that there is a limited support of nested types. It is in principle not supported on Beats and it has limited support in Kibana.

7

Thanks @jsoriano. I could not understand script well. If possible could you please share sample query using script for my scenario.

8

I think something like this could work for your case:

POST metricbeat-*/_search?size=0
{
    "aggs" : {
        "application_count" : {
            "cardinality" : {
                "script": {
                    "lang": "painless",
                    "source": "doc.application.stream().sorted().collect(Collectors.joining(':'))"
                }
            }
        }
    }
}

It sorts and joins all the application names, and then calculate the cardinality of the resulting field.

9

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.