Using SQL for Top Queries and Top Viewed Pages

Use SQL to get SearchBlox top queries and top viewed documents directly from Elasticsearch

🚧

Important Information:

You can find the Elasticsearch login credentials in the searchblox.yml file, found in the following file path:
\SearchBloxServer\webapps\searchblox\WEB-INF

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


What’s Next

You can go through the related sections below