# Visualisation from Kibana using SQL query
**URL:** https://discuss.elastic.co/t/visualisation-from-kibana-using-sql-query/211676
**Category:** Kibana
**Created:** [December 12, 2019, 3:02pm UTC](https://discuss.elastic.co/t/visualisation-from-kibana-using-sql-query/211676 "2019-12-12T15:02:44Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@mindbee](https://discuss.elastic.co/u/mindbee)
#### Post date: [December 12, 2019, 3:02pm UTC](https://discuss.elastic.co/t/visualisation-from-kibana-using-sql-query/211676/1 "2019-12-12T15:02:44Z")
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
```auto
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:**
```auto
{
"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?
---
### Author:  [@Brandon\_Kobel](https://discuss.elastic.co/u/Brandon_Kobel)
#### Post date: [December 16, 2019, 5:29pm UTC](https://discuss.elastic.co/t/visualisation-from-kibana-using-sql-query/211676/2 "2019-12-16T17:29:41Z")
Hey @mindbee [Kibana Canvas](https://www.elastic.co/guide/en/kibana/current/canvas.html) 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](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-selector-aggregation.html#search-aggregations-pipeline-bucket-selector-aggregation) which isn't currently supported.
---
### Author:  [@mindbee](https://discuss.elastic.co/u/mindbee)
#### Post date: [December 17, 2019, 2:15pm UTC](https://discuss.elastic.co/t/visualisation-from-kibana-using-sql-query/211676/3 "2019-12-17T14:15:51Z")
Thanks @Brandon_Kobel, will give it try.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [January 14, 2020, 2:15pm UTC](https://discuss.elastic.co/t/visualisation-from-kibana-using-sql-query/211676/4 "2020-01-14T14:15:57Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.