Visualize sql query result

[asadiya (Anan Sadiya)]

Hi,

How can visualize the result of a sql query in Kibana?

For example:
Query:

POST _xpack/sql?format=txt
{
"query":"SELECT ip_client, count(*) AS Number_Logins FROM splunk_logs WHERE result='000' AND channel='WEB' AND action='LOGIN' GROUP BY ip_client"
}

Result:

ip_client | Number_Logins
---------------+---------------
83.61.23.163 |9

Thanks.

[Bargs (Matt Bargar)]

Core Kibana does not have SQL support yet, but the new Canvas application does.

[asadiya (Anan Sadiya)]

Thanks, i just tried Canvas but still have the same problem.
Query:

SELECT(SELECT * FROM splunk_logs WHERE action='LOGIN')-(SELECT * FROM splunk_logs WHERE action = 'VOTST') AS Diff

No result.. and if i execute this query in Dev Tools:

POST _xpack/sql?format=txt
{
"query":"SELECT(SELECT * FROM splunk_logs WHERE action='LOGIN')-(SELECT * FROM splunk_logs WHERE action = 'VOTST') AS Diff"
}

Result:

{
"error": {
"root_cause": [
{
"type": "unsupported_operation_exception",
"reason": null
}
],
"type": "unsupported_operation_exception",
"reason": null
},
"status": 500
}

There is any limitation for sql queries in Elasticsearch?
Thanks again.

[Bargs (Matt Bargar)]

There are definitely some differences but I'm not intimately familiar with the details myself. For help with specific SQL queries, try the Elasticsearch forum.