Using SQL for Top Queries and Top View Pages
Use SQL to get SearchBlox top queries and top viewed documents directly from Elasticsearch
Important Information:
Credentials for Elasticsearch https://localhost:9200
Username : admin
Password: xxxxx
Query Logs
The query logs in elasticsearch can be downloaded in CSV using the following SQL:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from querylogs
The search queries can be downloaded using the following SQL:
https://localhost:9200/_opendistro/_sql?format=csv&sql=select * from querylogs group by querystring
You can get the number of hits for each query from elasticsearch using the following SQL:
http://localhost:9200/_opendistro/_sql?format=csv&sql=select count(*) from querylogs group by querystring
Most Popular Search Results
The most popular or top-viewed search results can be downloaded in CSV using the following SQL:
http://localhost:9200/_opendistro/_sql?format=csv&sql=select * from idx9998
You can get the URL and number of times they will clicked from elasticsearch search results in CSV using the following SQL:
http://localhost:9200/_opendistro/_sql?format=csv&sql=select url, clickcount from idx9998
Using SQL, you can filter and search the data from elasticsearch. For examples, please refer Using SQL in Elasticsearch
Updated over 2 years ago