# Sql query with like in "elasticSearch Sql" bugs?

**URL:** <https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621>\
**Category:** Elasticsearch\
**Created:** [October 1, 2022, 3:49pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621 "2022-10-01T15:49:35Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 1, 2022, 3:49pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/1 "2022-10-01T15:49:35Z")

</div>

i' ve simple query but not have resulset with

```auto
SELECT * FROM "errors_prima*"

where message like '%fiscal%' (sorry is closed on test not is the problem)

```

but if use

```auto

SELECT * FROM "errors_prima*"

where message like '%(No message present%

```

have results

if use with no filter have meny record and exist a string  
`Author fiscal

code can't be empty`

`SELECT * FROM "errors_prima*"`

| @timestamp | @version | host | message | path |
| --- | --- | --- | --- | --- |
| 2022-09-30T19:22:14+02:00 | 1 | 8d2871a9f264 | 2022-09-22 14:48:30,538 ERROR (\<?xml version="1.0" encoding="UTF-8" standalone="yes"?\>\<xds:response xmlns:xds="[Open eHealth Foundation · GitHub](http://www.openehealth.org/ipf/xds)"\>xds:statusFailure\</xds:status\>xds:errorxds:errorCodeLocalPolicyRestrictionError\</xds:errorCode\>xds:codeContextAuthor fiscal code can't be empty\</xds:codeContext\>xds:severityError\</xds:severity\>\</xds:error\>\</xds:response\>) | |

| @timestamp | @version | host | message | path |
| --- | --- | --- | --- | --- |
| 2022-09-30T19:22:40+02:00 | 1 | 8d2871a9f264 | 2022-09-27 17:12:36,336 ERROR (TcpSocketConsumerRunnable[mllp://0.0.0.0:9010] - /192.168.0.7:56174 =\> /192.168.0.32:9010) Invalid MDM message (No message present) | /logstash\_dir/P1/server.log.2022-09-27 |

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [October 1, 2022, 4:40pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/2 "2022-10-01T16:40:37Z")

</div>

How are you submitting the SQL queries through which interface?

One thing I notice is that you do not have a closing `'`

`where message like '%fiscal%`  
should be  
`where message like '%fiscal%'`. \<!---- Closing Single Quote

Perhaps try that....

You can test your Queries From Kibana Dev Tools

The look like this ... you have to do some Escaping

```auto
POST _sql?format=txt
{
  "query":"SELECT \"@timestamp\", service.name, cloudfoundry.app.name as app FROM \"filebeat-*\" WHERE app LIKE '%data%' LIMIT 10"
}

```

```auto
       @timestamp | service.name | app         
------------------------+---------------+---------------------
2022-09-25T08:55:07.422Z|cardatabase |cardatabase-back-end 
2022-09-27T09:17:39.745Z|cardatabase |cardatabase-back-end 
2022-09-29T09:35:37.762Z|null |cardatabase-back-end 
2022-09-30T09:47:32.683Z|null |cardatabase-back-end 
2022-09-09T06:17:47.998Z|null |cardatabase-front-end
2022-09-10T06:27:50.242Z|cardatabase |cardatabase-back-end 
2022-09-14T07:07:49.476Z|cardatabase |cardatabase-back-end 
2022-09-18T07:47:51.706Z|cardatabase |cardatabase-back-end 
2022-09-19T07:57:50.910Z|cardatabase |cardatabase-back-end 
2022-09-21T08:17:34.757Z|cardatabase |cardatabase-back-end 

```

---

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 1, 2022, 4:59pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/3 "2022-10-01T16:59:00Z")

</div>

yes i close the quote in original test error is in post this is my dashboard image

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/8/28ff31e956fb825ab10350dd1e8b1e669bc31be4.png)

---

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 1, 2022, 5:02pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/4 "2022-10-01T17:02:29Z")

</div>

![image](https://us1.discourse-cdn.com/elastic/original/3X/1/3/13dc7dcf7af2c8aaed17835ce2585a3bd58ada83.png)

---

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 1, 2022, 5:03pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/5 "2022-10-01T17:03:24Z")

</div>

very mistake same index have other string inner

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/3/23832d4c622f54c9128b4e223a294a726612359f.png)

---

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 1, 2022, 5:05pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/6 "2022-10-01T17:05:57Z")

</div>

if search all "fiscal" string exists

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/c/2c23521937e8cfc827b28fee318884b767d9558d.png)

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [October 1, 2022, 5:51pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/7 "2022-10-01T17:51:42Z")

</div>

What Elastic version are you on?

BTW Leading `%like` are _ **VERY** _ inefficient and could negatively impact your cluster on large data sets

Can you try the same from Kibana -\> Dev Tools see the results?

You are doing this from canvas correct? Yes I see.

And very important what is the type of `message` field

Stack Management -\> Data Views -\>

 ![Screen Shot 2022-10-01 at 2.10.09 PM](https://us1.discourse-cdn.com/elastic/original/3X/c/f/cfdcfd93bc4f4b94ebdc1c212375dc9b94af2265.jpeg)

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [October 1, 2022, 9:28pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/8 "2022-10-01T21:28:39Z")

</div>

Not sure what is going on... I would make a quick data table... and check everything  
the %value% will not work on a data type `text` will only work on a `keyword`

Top Table and Metric

```auto
SELECT "@timestamp", url.path, cloudfoundry.app.name FROM ".ds-filebeat-8.2.3-2022.10.01-000304" WHERE cloudfoundry.app.name IS NOT NULL and url.path IS NOT NULL

```

Bottom Table and Metric

```auto
SELECT "@timestamp", url.path, cloudfoundry.app.name FROM ".ds-filebeat-8.2.3-2022.10.01-000304" WHERE cloudfoundry.app.name IS NOT NULL and url.path LIKE '%api%'

```

 ![Screen Shot 2022-10-01 at 2.44.45 PM](https://us1.discourse-cdn.com/elastic/original/3X/b/4/b48c140e22012e1d5a31fa154f0399c605618664.jpeg)

I will admit Canvas is **not** my favorite Vis Tool...

You could probably build all this with a Dashboard and Lens in like 10 Mins... much easier..

[Using KQL much easier](https://www.elastic.co/guide/en/kibana/current/kuery-query.html)... fast / efficient.

Each of these took me 30 sec each

 ![Screen Shot 2022-10-01 at 3.00.44 PM](https://us1.discourse-cdn.com/elastic/original/3X/a/1/a137c543545b9aee188239b1810583e5a217e8d6.png)  
 ![Screen Shot 2022-10-01 at 3.00.31 PM](https://us1.discourse-cdn.com/elastic/original/3X/7/1/719089f77b69af4028149e4637e59c338761ed1a.png)

Unless you are really looking to some special canvas work I would use Regular Dashboards and Lens

3 of them on a dashboard plus the Table less than 5 mins BUT `*token*` just like the like is very inefficient just keep that in mind..

 ![Screen Shot 2022-10-01 at 3.11.34 PM](https://us1.discourse-cdn.com/elastic/original/3X/1/b/1b3dc59c97d0d5c97b363d52160eafef1d42b807.png)

---

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 3, 2022, 9:26am UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/9 "2022-10-03T09:26:05Z")

</div>

hello i use version 7.16.2 basic

---

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 3, 2022, 9:26am UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/10 "2022-10-03T09:26:58Z")

</div>

this is stack management

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/6/f/6f1d1d0451a1ee9ba832de3004b21de1b393ec9f.png)

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [October 3, 2022, 4:12pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/11 "2022-10-03T16:12:40Z")

</div>

Ok ... Everything I stated above should be pretty valid.  
There may not be the brand new metrics widget in lens.  
I will go back you could create this in Lens without all the SQL.  
I did get SQL to work in Canvas but it appears to be a bit "finicky"

---

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 3, 2022, 4:27pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/12 "2022-10-03T16:27:32Z")

</div>

i' ve used docker-compose version logstash+elastic++kibana i dont see a filebeat but the file is indexed

but I see strange things in the query

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [October 3, 2022, 4:37pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/13 "2022-10-03T16:37:14Z")

</div>

Yes as I said Canvas + SQL seem to have a few issues and is hard to use (just my opinion especially for a simple dashboard) ... BUT I did get it to work... it is very picky / sensitive ... changing too fast can cause issues... syntax errors, large data set .. etc... wildcards etc.

My Suggestion 🙂

A regular Dashboard + Lens + KQL will get you there much faster / easier... MUCH faster / easier

Just my Suggestion

if you want help on filebeat please open a separate topic.

---

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 3, 2022, 5:42pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/14 "2022-10-03T17:42:26Z")

</div>

hello do you have a docker-compose specific version to advise me,  
that has been tested well?

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [October 3, 2022, 5:54pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/15 "2022-10-03T17:54:45Z")

</div>

See [Start a multi-node cluster with Docker Compose](https://www.elastic.co/guide/en/elasticsearch/reference/current/docker.html#docker-compose-file)

I run this all the time...

I still think perhaps you may be more successful with Normal Dashboards + Lens instead of Canvas + SQL it is just my experience / suggestion.

---

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 4, 2022, 1:02pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/16 "2022-10-04T13:02:44Z")

</div>

hello i've create the docker-compose with cluster by at the end of create receive the error

```auto

ERROR: for kibana Container "06c680e075d8" is unhealthy.
ERROR: Encountered errors while bringing up the project.

```

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [October 4, 2022, 2:08pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/17 "2022-10-04T14:08:56Z")

</div>

Please open a separate thread for the docker question, please include your entire docker compose we can not help with just the error message.

---

<div class="post-metadata">

**Author:** ![Nikolas1306](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nikolas1306/32/111507_2.png) [@Nikolas1306](https://discuss.elastic.co/u/Nikolas1306)\
**Post date:** [October 4, 2022, 2:22pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/18 "2022-10-04T14:22:41Z")

</div>

> [@Nikolas1306](#):
>
> hello i've create the docker-compose with cluster by at the end of create receive the error
> 
> ```auto
> ERROR: for kibana Container "06c680e075d8" is unhealthy.
> ERROR: Encountered errors while bringing up the project.
> 
> ```

ok tnx

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 1, 2022, 2:22pm UTC](https://discuss.elastic.co/t/sql-query-with-like-in-elasticsearch-sql-bugs/315621/19 "2022-11-01T14:22:55Z")

</div>

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