Hi community
I need to obtain in a dashboard, two specific queries to the databases of all the agents from which I receive data (for this purpose it is this entire project among other things for which I have gotten involved with Elastic)
I have the integration with PostgreSQL done that comes by default, but I don't know if it is the correct way to do it, since I know that ES cannot read data from PostgreSQL directly, and I have to do these two queries to obtain data:
select * from cc_usertoinvoiceview;
select * from cc_version;
I'm not sure you are asking the right question or using the right tool.
Are you saying that you want to fetch the content of 2 tables into Elasticsearch?
If so, I don't see the relationship with "agents" which are here to collect logs and metrics...
I still don't see how "select * from cc_usertoinvoiceview;" relates to Elastic Fleet Server...
May be someone else has a better understanding than me and could help.
@dadoonet It is an example of one of the queries that I want to make against the databases that run in Postgresql on my agents/remote machines.
What I want is to make a certain query on the databases and have the data returned to me, and that data to be displayed on a dashboard.
I don't understand that you don't understand... it's normal.
The same as with any integration, but with specific queries.
For example, these integrations are to visualize metrics and data from the databases of the remote agents/servers connected to my elastic.
Collecting PostgreSQL logs is not related to collect the content of the database itself.
That's why it's a different use case and a different tool.
In short:
You need logs or metrics (because you are a DBA for example), use Elastic Agent/Fleet with the PostgreSQL integration
You need to collect data from a database, because you are a end user, a developer.... Use The PostgreSQL connector or build your own thing from the application which is writing the data to the database.
Once again thanks for the response.
I need exactly what I mentioned in my first post.
Perform two specific queries against those databases, which return the result, and display that result on a dashboard.
To give an example, these databases save in one of the tables the number of users connected to the application, with which that database is connected.
By doing a query against that database (just to give an example, I'm not saying that it is exactly select * from cc_usertoinvoiceview;) I get the number of connected users as a result.
Well, the same thing, but view that data in a dashboard in Kibana.
Thank you.
So you need to run every x minutes/seconds a script which fetches that information from the database.
Then build a json document and send it to Elasticsearch.
Then you can visualize that information in Kibana.
Once again @dadoonet , thanks for the help.
I understand that it is not possible to execute the query from the same PostgreSQL integration, right? Is that option not viable?
On the other hand, given your experience, of the options you give me 2 and 3, which one do you recommend the most?
Thank you.
Send your existing data to elasticsearch. That means:
Read from the database (SELECT * from TABLE)
Convert each record to a JSON Document
Send the json document to elasticsearch, preferably using the _bulk API.
Logstash can help for that. But I'd recommend modifying the application layer if possible and send data to elasticsearch in the same "transaction" as you are sending your data to the database.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.