Actual Query for Actions > Page Titles

Enabling debug doesn’t give the actual queries used to calculate the page views, unique page views, etc. How do I get those queries?

James Beauchamp

To be more specific…

I need to replicate this report on JasperServer with additional data from Joomla. We have the extension installed which pushes the titles to Piwik and allows Piwik to show the hierarchy of the Joomla categories. Is there a way to see the raw queries before the archive is created or better yet can someone just give me the query used to create that view?



Here are my queries:

CREATE TABLE IF NOT EXISTS unique_pageviews (
la_url.idaction AS article_url_id, AS article_url,
la_name.idaction AS article_title_id, AS article_title
FROM piwik_log_link_visit_action llva
LEFT JOIN piwik_log_action AS la_url
ON llva.idaction_url = la_url.idaction
LEFT JOIN piwik_log_action AS la_name
ON llva.idaction_name = la_name.idaction
WHERE llva.idsite = 1
AND llva.idaction_name IS NOT NULL
AND llva.server_time BETWEEN ‘2014-02-19 00:00:00’ AND '2014-02-19 23:59:59’
GROUP BY llva.idvisit, llva.idaction_url
ORDER BY idvisit);

SELECT SUM(sub.pageviews) AS total_unique_pageviews
COUNT(*) AS pageviews
FROM unique_pageviews
WHERE article_title LIKE 'ELC/%'
GROUP BY article_url) AS sub;