Inverse Query

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?


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


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?

Unfortunately that's a bug. I created an issue and we'll look at it with priority:

1 Like

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:

1 Like

Has it been fixed in release 6.5.4?

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

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