Using SQL for Top Queries and Top Viewed Pages

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

🚧

Important Information:

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

Query Logs

The query logs in opensearch can be downloaded in CSV using the following SQL:

https://localhost:9200/_plugins/_sql?format=csv&sql=select * from querylogs

The search queries can be downloaded using the following SQL:

https://localhost:9200/_plugins/_sql?format=csv&sql=select * from querylogs group by querystring

You can get the number of hits for each query from opensearch using the following SQL:

http://localhost:9200/_plugins/_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/_plugins/_sql?format=csv&sql=select * from idx9998

You can get the URL and number of times those URL is clicked from opensearch 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 opensearch. For examples, please refer Using SQL in Opensearch