ComboStrap Analytics - How to query the analytics database

How-to

This page will show you how to query the analytics database online.

Steps

Go to the admin page

Click on the Admin link to go to the Admin Window. ie https://yourdomain.com/?do=admin

ComboStrap Template Dokuwiki Template

Go to the Sqlite Interface

The SQLite interface is a webpage that permits to send statement to the SQLite database.

To access it directly, the URL is https://yourdomain.com/?do=admin&page=sqlite

Choose the database

Choose the Combo database.

If you were users:

  • of the 404Manager, the database may be named 404manager
  • of the webcomponent plugin, the database may be named webcomponent

The URL to access your database will look like the following URL

https://yourdomain.com/?do=admin&page=sqlite&db=combo&version=sqlite3

Json Support

You need to have sqlite with json support.

  • The sqlite database version should be greater or equal to SQLite 3.9.0 (2015-10-14).
select sqlite_version();
^ sqlite_version ^
| 3.28.0 |
  • The json module should be present when executing the below SQL.
PRAGMA compile_options;
^ compile_options ^
| ENABLE_JSON1 |

Enter your query and submit

Enter the analytical query and submit.

You can try the below query that returns statistics over words from the json analytics column.

select
	count(*) as page_count,
	round(avg(words)) as words_avg,
	sum(words) as words_count,
	max(words) as words_max,
	min(words) as words_min
from
	(
	select
		id,
		json_extract(analytics , '$.statistics.words') as words
	from
		pages
	where
		analytics is not null
)
Powered by ComboStrap