Kibana Canvas querying

[Vladpov]

Hi everyone!

I have log messages that look like "number.suffix" and the message can have two different suffix values either dn or mt. If message has the same number and both suffix are occurred it means that log message is "good" otherwise is log massage "wrong"

In my dataframe I have all messages and I'd like to see Pie chart how many percent of them have both suffix (mt and dn).

Is it even possible?

Thank you for any help in advance.

[Brandon_Kobel]

Hey @Vladpov, does each document only have a single "message" field, or are there 2 fields?

[Vladpov]

Hey, thank you for reply!

I'm not pretty sure what do you mean by single message field but I can show you the example of my log messages:

Jul 23 09:24:18 mmr mmr-core[5210]:  Aweg3AOMTs_1563866656876839.0.dn processDN() #7750 realtime: 1.382 ms

Jul 23 09:24:16 mmr mmr-core[5147]:  Aweg3AOMTs_1563866656876839.mt processMTMessage() #12798 realtime: 5.684 ms

Jul 23 09:44:14 mmr mmr-core[5207]:  EuroSMSBulk_1563867854324841.mt processMTMessage() #13025 realtime: 4.961 ms

Jul 23 09:44:18 mmr mmr-core[5166]:  EuroSMSBulk_1563867854324841.0.dn processDN() #8006 realtime: 1.84 ms

Jul 23 09:44:14 mmr mmr-core[5207]:  EuroSMSBulk_1563867854324841.mt NPDB::query(420=npdbcz,603349037): no match [1.58 ms]

I parsed these log messages into num1 part which is the number before .mt or .dn and suffix which is dn or mt so I can search them...

Hope it helped you to understand what I meant

Of course there could be a lot of the same numbers which I use like an id and there could be many id's only with suffix dn or only with mt. I need to compare numbers of messages that have both suffix's with numbers with only one suffix.

[Brandon_Kobel]

Thanks for the additional clarification. Based on my understanding the sample log message would lead to 5 documents being created, similar to the following:

{ "num1": 1563866656876839.0, "suffix": "dn" }
{ "num1": 1563866656876839, "suffix": "mt" }
{ "num1": 1563867854324841, "suffix": "mt" }
{ "num1": 1563867854324841.0, "suffix": "dn" }
{ "num1": 1563867854324841, "suffix": "mt" }

So, for each of these documents we'd like to see if there is another document with the same num1, but with the alternate suffix.

Unfortunately, this would require a left join, which Elasticsearch does not support. Elasticsearch has very limited join support and this is reflected in Elasticsearch SQL, which is how Canvas allows you to query documents.

In situations like these, we generally recommend that users augment their ingest process to store their documents in such a way that these types of questions can be answered.

[Vladpov]

I acctually achieved what I wanted with this code:

GET /my_index3/_search
{
  "size": 0,
  "aggs": {
    "num1": {
      "terms": {
        "field": "num1.keyword",
        "order": {
          "_count": "desc"
        }
      },
        "aggs": {
          "count_of_suffix": {
            "cardinality": {
              "field": "suffix.keyword"
            }
          },

            "my_filter": {
              "bucket_selector": {
                "buckets_path": {
                  "count_of_suffix": "count_of_suffix"
                },
                "script": "params.count_of_suffix == 2"
              }
            }
        
      }
    }
  }
}

It gives me output:

  "aggregations" : {
    "num1" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 8,
      "buckets" : [
        {
          "key" : "1563866656876839",
          "doc_count" : 344,
          "count_of_suffix" : {
            "value" : 2
          }
        },
        {
          "key" : "1563866656878888",
          "doc_count" : 322,
          "count_of_suffix" : {
            "value" : 2
          }
        },
        {
          "key" : "1563867854324841",
          "doc_count" : 50,
          "count_of_suffix" : {
            "value" : 2
          }
        },
        {
          "key" : "1563866656871111",
          "doc_count" : 40,
          "count_of_suffix" : {
            "value" : 2
          }
        }
      ]
    }
  }

And in Canvas I made it with following query

filters
| essql 
  query="select num1.keyword from dataframelast where suffix.keyword in ('mt','dn') group by num1.keyword having count (distinct suffix.keyword) = 2"
| pie 
| render

The problem now is that it shows me 100% one coloured Pie. Can I somehow write another query to divide the Pie into two colours for example 10% what is the result I got from first query would be blue and rest that don't satisfy my requirement would be red...