Visualize sql query result


(Anan Sadiya) #1

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.


(Matt Bargar) #2

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


(Anan Sadiya) #3

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.


(Matt Bargar) #4

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.