How to do a select on every objects in Elasticsearch

Elastic Stack Elasticsearch
1

Hi all

I'm exploring the ES SQL and I need more information. I only read the getting start on the documentation about SQL.

Is it possible to do a select on logstash or other index present in elastic?

I read examples on flights, but I don't understand where I could find the information about this table in the index pattern.

Thank you
Franco

2

Hi @franco.federico,

It's not entirely clear for me what you are looking for.
If I got your question right, you can find more information about tables using SHOW TABLES command or DESCRIBE TABLE.

Also, you can use a pattern if you want to query multiple tables.

3

If I want to do an ES SQL query on logstash index, what is the name of the table or the schema that implement logstash index?

And if I want to do an ES SQL query on other index what is the name of the table or the schema that show that implement a generic index?

Thank you
Franco

4

I am on the demo.elastic.com
I see filebeat index.

image
image.png1920×470 57.5 KB

Now I'm trying to do a describe of filebeat in the dev tool and I have no response

image
image.png1158×298 22.2 KB

Why?

I try with other index but I have the same result. Only for flights it's done.

Thank you
Franco

5

I'm reading in the documentation that the table is the index.

image
image.png1089×793 75.8 KB

In this case it doen't work? Why? I tried to my system with the X Pack active on the versione 6.7 but I have the same problem.

Is there other configuration to do?

Thank you
Franco

6

Because filebeat is not a table/index. By default, Filebeat writes events to multiple indices (daily) named filebeat-[Filebeat_version]-yyyy.MM.dd, where yyyy.MM.dd is the date when the events were indexed.

In demo.elastic.co, you should use

POST _sql?pretty&format=txt
{
  "query":"SHOW TABLES LIKE 'filebeat%'"
}

to see the list of filebeat indices and then have a look at one of them with DESCRIBE sql command. For example:

POST _sql?pretty&format=txt
{
  "query":"DESCRIBE \"filebeat-7.0.0-2019.05.07-000050\""
}
7

Thank you for information @Andrei_Stefan.

I suggest to add this example in the documentation because a basic user when found the SQL the first step is to try to exist index.

Bye
Franco