Scripted field from sql api not working

Elastic Stack Elasticsearch
1

Hi All,

I recently started using sql from elasticsearch. i am trying to use below API

POST _sql?format=csv
{
"query": "SELECT SERVICE_NAME,Success,count() From"my_inde" WHERE "@timestamp" >= NOW() - INTERVAL 3 HOURS group by SERVICE_NAME,Success"
}

where Success is my scripted field. However I am getting below error.

{
"error" : {
"root_cause" : [
{
"type" : "verification_exception",
"reason" : "Found 1 problem\nline 1:21: Unknown column [Success]"
}
],
"type" : "verification_exception",
"reason" : "Found 1 problem\nline 1:21: Unknown column [Success]"
},
"status" : 400
}

Can someone please help

2

What are the documents?

Please format your code, logs or configuration files using </> icon as explained in this guide and not the citation button. It will make your post more readable.

Or use markdown style like:

```
CODE
```

This is the icon to use if you are not using markdown format:

image
image764×92 16.4 KB

There's a live preview panel for exactly this reasons.

Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text.
If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible.

3

If Success was created with Kibana's scripted fields functionality, you won't be able to use it in SQL since those are accessible to Kibana only.
However, you might potentially be able to compute that field as an SQL expression and potentially also group by that expression (or it's aliased name). Something along the lines of:

SELECT SERVICE_NAME, some_field > 0 AS Success, count() FROM "my_index" WHERE "@timestamp" >= NOW() - INTERVAL 3 HOURS GROUP BY SERVICE_NAME, Success
4

@bogdan.pintea : Thanks a lot it is almost working , I am getting below response. Can we print success/failure instead of true false. I am getting below output.

Query :

SELECT SERVICE_NAME,ERRORSTATUS.keyword ='0' AS Success,count() FROM "my_visualization" WHERE "@timestamp" >= NOW() - INTERVAL 1 HOURS and SERVICE_NAME IN ('My_service') group by SERVICE_NAME,Success

SERVICE_NAME,Success,count(*)
SUB_CHURN,false,1652
SUB_CHURN,true,5

5

Have a look at the CASE doc. Something like: ... CASE WHEN ERRORSTATUS.keyword ='0' THEN 'success' ELSE 'failure' END ...

6

Thanks @bogdan.pintea : Is there a way to find the difference of count of these two records

7

You'll probably want something like SELECT count(ERRORSTATUS.keyword) FROM .. GROUP BY ERRORSTATUS.keyword. It's standard SQL, any good tutorial should touch on it.

8

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