Nested subquery similar to NOT IN SQL syntax

[prwillmot (Peter Willmot)]

Am am trying to use ELK stack to monitor status of many 1000s of "terminals" in our environment

we have built an index that contains timestamp and terminal_id (along with other terminal properties) - ingested via filebeat / logstash hierarchy into elastic - this all works perfectly so far

One specific business requirement is to produce a daily report listing terminals that have started for the first time

I am a noob for ELK (and am loving it!) but do have a reasonably strong RDBMS background - in MS SQL, we could have solved this quite simply as follows:

SELECT terminal_id
FROM terminals
WHERE DATE(timestampt) = DATE(GETDATE()) AND terminal_id NOT IN
(
SELECT DISTINCT terminal_id FROM terminals WHERE DATE(timestamp) < DATE(GETDATE())
)

After much searching of elastic docs and blogs - and quite a few false starts - it seems that my only option is to somehow persist the list of "known terminals" as a separate index and then build an "application-side join" in which I use the "known terminals" set as an exclusion list for the daily query

I was hoping that the new Elastic SQL support would help but according your docs (notably https://www.elastic.co/blog/an-introduction-to-elasticsearch-sql-with-practical-examples-part-2 on 19 July 2018) SQL nested SELECTs are not supported in current release

Am I missing something?

[Andrei_Stefan]

@prwillmot you didn't miss anything. At the moment (no concrete plans for very near future either) nested select is not supported.

The idea is that fundamentally Elasticsearch is not doing well in a purely relational DB kind of indices setup. When moving from RDBMS to ES there has to be a step in which the structure of the indices needs to be assessed and more or less the DB tables need to be denormalized. There are use cases where parent-child or nested documents are preferred, but still there is a need to investigate the setup.

[prwillmot (Peter Willmot)]

Many thanks - I will investigate maintaining a set of purpose-defined indexes to do this with an application-side JOIN

I suspect that even though this does seem tedious (if compared to a more simple SQL approach) we believe that overall ELK will offer us huge benefits, so paying a small "complexity price" for a couple of use cases (out of many) is not a show-stopper