Hi Team,
Would like to check in this forum, Is there anyway that we can achieve UNION by using KQL?
I have a requirement where I would need to build a Kibana dashboard with UNION of multiple sub-queries.
Thank you!
I don't think KQL is the right answer here - it's a rough comparison, but it's basically just the WHERE part of a SQL query (The concepts of SELECT, GROUP BY and so on are managed by the individual visualizations).
Can you explain what you want to do exactly?
There is no support for UNION or UNION ALL in ES-SQL at the moment.
The only workaround is to query multiple indices with a pattern, e.g.:
SELECT * FROM "idx-*" WHERE ...
But this means that you have indices with compatible fields (same name and compatible data types), and that you apply the exact same WHERE clause, ORDER BY, etc. There is currently no support of UNIONing subqueries.
I have a single metricbeat index in which I have two records one record contains CPU metrics and another record contains memory metrics.
We want to show the server with more than 60% of memory or CPU utilization in a single data table.
Is this possible to achieve?
Timestamp HostName CPU Memory
15-06-2021 15:20:00 <HOSTNAME> 70% 80%
This should work with the basic tools available in Kibana:
- Create an index pattern which includes the indices in which CPU and memory metrics are stored
- Create a new Lens visualization and switch to data table
- For rows, use a date histogram on your time field and top values of the host name
- For metrics, use average of CPU and memory fields
Even if it's different documents containing cpu and memory data it's still possible to select them - the average calculation will simply ignore the empty fields
Noted with thanks for prompt reply Joe.
Will try this and get back to you incase of any help.
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.