Hello! I have two indexes that I'd like to compare:
- winlogbeat-*
- winlogbeat_rollup
winlogbeat_rollup is the rollup of winlogbeat-* and contains its historical data. What I'm looking to do is to compare these two indexes using the common field, "host.name". I'd like this comparison query to return the host.name values that are present in winlogbeat_rollup (historical data) but are not present in winlogbeat-*. The idea is to eventually use a time constraint on winlogbeat-* so that the query returns host names that I'm no longer receiving records for.
I figured the easiest way to do this was with the following SQL query:
POST _sql?format=txt
{
"query" : """
SELECT host.name.terms.value as Server FROM "winlogbeat_rollup"
EXCEPT
SELECT host.name as Server FROM "winlogbeat-*"
"""
}
However this returns the following parsing error:
line 4:3: mismatched input 'SELECT' expecting {<EOF>, ',', 'FULL', 'GROUP', 'HAVING', 'INNER', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'ORDER', 'PIVOT', 'RIGHT', 'WHERE', LIMIT_ESC}
Is the SQL Except supported in Kibana? If not, is there another way I can form a query that will provide me with the results I'm looking for?