Visualisation from Kibana using SQL query

Elastic Stack Kibana
1

Hi,

I have results correct getting from SQL query using devtools of Kibana. But not sure how I can translate into the visualization of kibana.

Below are the things done using devtool:
POST _sql?format=txt
{
"query": "SELECT max(responseTime) FROM "test-logs" GROUP BY transactionID.keyword HAVING count(transactionID.keyword)=2 "
}

which gave me below result set

max(responseTime)
-----------------
266.0
156.0
412.0

Then I have translated the sql which is below into json

GET /test-logs/_search
{
  "size" : 0,
  "_source" : false,
  "stored_fields" : "_none_",
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 1000,
        "sources" : [
          {
            "2619" : {
              "terms" : {
                "field" : "transactionID.keyword",
                "missing_bucket" : true,
                "order" : "asc"
              }
            }
          }
        ]
      },
      "aggregations" : {
        "2623" : {
          "max" : {
            "field" : "responseTime"
          }
        },
        "2619" : {
          "filter" : {
            "exists" : {
              "field" : "transactionID.keyword",
              "boost" : 1.0
            }
          }
        },
        "having.2627" : {
          "bucket_selector" : {
            "buckets_path" : {
              "a0" : "2619._count"
            },
            "script" : {
              "source" : "InternalSqlScriptUtils.nullSafeFilter(InternalSqlScriptUtils.eq(params.a0,params.v0))",
              "lang" : "painless",
              "params" : {
                "v0" : 2
              }
            },
            "gap_policy" : "skip"
          }
        }
      }
    }
  }
}

Response for it below:

{
  "size" : 0,
  "_source" : false,
  "stored_fields" : "_none_",
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 1000,
        "sources" : [
          {
            "2619" : {
              "terms" : {
                "field" : "transactionID.keyword",
                "missing_bucket" : true,
                "order" : "asc"
              }
            }
          }
        ]
      },
      "aggregations" : {
        "2623" : {
          "max" : {
            "field" : "responseTime"
          }
        },
        "2619" : {
          "filter" : {
            "exists" : {
              "field" : "transactionID.keyword",
              "boost" : 1.0
            }
          }
        },
        "having.2627" : {
          "bucket_selector" : {
            "buckets_path" : {
              "a0" : "2619._count"
            },
            "script" : {
              "source" : "InternalSqlScriptUtils.nullSafeFilter(InternalSqlScriptUtils.eq(params.a0,params.v0))",
              "lang" : "painless",
              "params" : {
                "v0" : 2
              }
            },
            "gap_policy" : "skip"
          }
        }
      }
    }
  }
}

Can some one help to translate this into Kibana visualisation?

2

Hey @mindbee Kibana Canvas allows you to use SQL directly as the data-source for a Visualization.

Using the "standard visualizations" in Kibana can get us mostly there; however, the HAVING count(transactionID.keyword)=2 part of the query must be expressed using a Bucket Selector Aggregation which isn't currently supported.

3

Thanks @Brandon_Kobel, will give it try.

4

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