Using SQL for Top Queries and Top Viewed Pages

This section explains how to use SQL queries to extract useful analytics data directly from OpenSearch — specifically the most frequently searched queries and the most viewed or clicked documents. This data is stored in dedicated OpenSearch indices and can be downloaded in CSV format for reporting or further analysis.

🚧

Important Information:

All requests to OpenSearch require authentication. You can find the username and password needed to access OpenSearch stored in the searchblox.yml configuration file at the following 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

If you need to apply additional filters, combine conditions, or run more advanced queries against the querylogs or any other OpenSearch index, refer to the full SQL query guide for detailed examples and supported query types: Using SQL in Opensearch