Inverse Query

[rudyamid]

I'm trying to create a Canvas markdown element with data from the following Elasticsearch SQL query:

SELECT Severity FROM "firewall-logs*" WHERE Severity LIKE '%critical%' AND ThreatName.keyword IS NOT NULL

My problem is, how do I get the inverse or "NOT LIKE" of the severity column (ie. that is not critical)? I tried using NOT LIKE and Canvas shows Expression failed with the message:

[essql] > Unexpected error from Elasticsearch: [sql_illegal_argument_exception] Cannot evaluate script for expression LikePattern[%critical%,]

Any ideas?

regards
Rudy

[rashmi (kulkarni)]

cc @Catherine_Liu @Joe_Fleming can either of you please take a stab at this ?

Thanks
Rashmi

[Joe_Fleming]

The problem is actually the quotes you are using. In SQL, ' and " mean different things. If you are just quoting a value to handle special characters, I believe you want to use double quotes (").

I get a different error when I use double quotes, and you might too:

SELECT extension,sum(bytes) AS bytes FROM "logstash*"
WHERE extension LIKE "%jpg%"
GROUP BY extension

[essql] > Couldn't parse Elasticsearch SQL query. You may need to add double quotes to names containing special characters. Check your query and try again. Error: [parsing_exception] line 2:22: mismatched input '"%jpg%"' expecting {'?', STRING}

I'm not totally sure the sql adapter supports LIKE and NOT LIKE yet... @costin can you chime in here?

[Andrei_Stefan]

Unfortunately that's a bug. I created an issue and we'll look at it with priority: https://github.com/elastic/elasticsearch/issues/36584

[costin (Costin Leau)]

To clarify a bit:

1. NOT LIKE currently doesn't work - there's a bug raised for that.
2. " are used for identifiers, ' is used for strings - this is all standard SQL.

The pattern matching supported by SQL is explained here:
https://www.elastic.co/guide/en/elasticsearch/reference/6.x/sql-index-patterns.html

[rudyamid]

Has it been fixed in release 6.5.4?

[Andrei_Stefan]

@rudyamid that issue was fixed and will be available in 6.6.0 and 7.0.0.