# View Query in PostgreSQL agent in dashboard **URL:** \ **Category:** Elasticsearch\ **Created:** [May 6, 2024, 2:47pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854 "2024-05-06T14:47:11Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Fernandoc4d](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fernandoc4d/32/134180_2.png) [@Fernandoc4d](https://discuss.elastic.co/u/Fernandoc4d)\ **Post date:** [May 6, 2024, 2:47pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/1 "2024-05-06T14:47:11Z")
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: ```auto select * from cc_usertoinvoiceview; select * from cc_version; ``` How do you recommend I do this? Thanks !!!!!!!!! ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [May 6, 2024, 3:03pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/2 "2024-05-06T15:03:03Z")
Hey 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](https://docs.elastic.co/en/integrations/postgresql)... May be you want to use the [PostgreSQL connector](https://www.elastic.co/guide/en/enterprise-search/current/postgresql-connector-client-tutorial.html)? Or you can also send your data from your application layer to Elasticsearch within the same transaction where you are sending the data to PostgreSQL. Could you tell more about what your are looking for? ---
**Author:** ![Fernandoc4d](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fernandoc4d/32/134180_2.png) [@Fernandoc4d](https://discuss.elastic.co/u/Fernandoc4d)\ **Post date:** [May 6, 2024, 3:22pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/3 "2024-05-06T15:22:24Z")
Hi @dadoonet and thanks for reply. I didn't explain myself well, sorry, I'll do it again. (All the machines that I am going to detail below are managed by me) 1. I have a server with ELASTIC+KIBANA 2. Connected to the fleet server on that same server, several remote agents/machines in production 3. Each of those servers runs PostgreSQL. 4. I need to make two queries on the databases of all those servers and display them in a dashboard in kibana. 5. I need to "paint" graphically on a dashboard as I said before, the data from those queries. I hope I have explained myself better, I attached a diagram. Thank you. ![Elastic+Kibana PSQL](https://us1.discourse-cdn.com/elastic/original/3X/f/8/f88d11362efc2dac4852fb9530948ba1d216ce47.jpeg) ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [May 6, 2024, 4:25pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/4 "2024-05-06T16:25:14Z")
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. ---
**Author:** ![Fernandoc4d](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fernandoc4d/32/134180_2.png) [@Fernandoc4d](https://discuss.elastic.co/u/Fernandoc4d)\ **Post date:** [May 6, 2024, 4:35pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/5 "2024-05-06T16:35:07Z")
@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. ![imagen](https://us1.discourse-cdn.com/elastic/original/3X/3/8/38f39a275cb1bb103ee9772f176370489674639e.png) ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [May 6, 2024, 4:42pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/6 "2024-05-06T16:42:52Z")
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. 2 different ways for 2 different use cases. ---
**Author:** ![Fernandoc4d](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fernandoc4d/32/134180_2.png) [@Fernandoc4d](https://discuss.elastic.co/u/Fernandoc4d)\ **Post date:** [May 6, 2024, 4:56pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/7 "2024-05-06T16:56:52Z")
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. ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [May 6, 2024, 5:39pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/8 "2024-05-06T17:39:04Z")
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. To perform the first steps, you can: - write your own code / script - use Logstash - use the PostgreSQL connector ---
**Author:** ![Fernandoc4d](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fernandoc4d/32/134180_2.png) [@Fernandoc4d](https://discuss.elastic.co/u/Fernandoc4d)\ **Post date:** [May 6, 2024, 6:26pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/9 "2024-05-06T18:26:10Z")
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. ![imagen](https://us1.discourse-cdn.com/elastic/original/3X/e/8/e839dac7e82a4070804ad074e6aaf7f74648471b.png) ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [May 6, 2024, 11:42pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/10 "2024-05-06T23:42:29Z")
My own preference is: 1, 3, 2. ---
**Author:** ![Fernandoc4d](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fernandoc4d/32/134180_2.png) [@Fernandoc4d](https://discuss.elastic.co/u/Fernandoc4d)\ **Post date:** [May 7, 2024, 2:47pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/11 "2024-05-07T14:47:23Z")
As extra information and another contribution to the possible solution. > **[GitHub - toluaina/pgsync: Postgres to Elasticsearch/OpenSearch sync](https://github.com/toluaina/pgsync)** > > Postgres to Elasticsearch/OpenSearch sync. Contribute to toluaina/pgsync development by creating an account on GitHub. ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [May 7, 2024, 2:58pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/12 "2024-05-07T14:58:01Z")
Some other ideas: 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. I shared most of my thoughts there: [https://david.pilato.fr/blog/2015-05-09-advanced-search-for-your-legacy-application/](http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/) Have also a look at this ["live coding" recording](https://www.elastic.co/fr/blog/how-to-add-powerful-search-existing-sql-applications-elasticsearch-video-tutorial). ---
**Author:** ![Fernandoc4d](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fernandoc4d/32/134180_2.png) [@Fernandoc4d](https://discuss.elastic.co/u/Fernandoc4d)\ **Post date:** [May 7, 2024, 3:04pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/13 "2024-05-07T15:04:08Z")
Thank you so much @dadoonet Now I will review all the documentation, but then would you recommend Logstash more? ... Would it be easier to implement? ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [May 7, 2024, 3:08pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/14 "2024-05-07T15:08:48Z")
As I said earlier, my personal preference goes from (1 has higher preference, 3 lesser preference): 1. write your own code / script 2. use the PostgreSQL connector 3. use Logstash ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 4, 2024, 3:09pm UTC](https://discuss.elastic.co/t/view-query-in-postgresql-agent-in-dashboard/358854/15 "2024-06-04T15:09:07Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.