SQL to see pages views with users

Question

Hi,

I need an SQL query that we can run in our confluence to get a list of users who view each pages (with the date they last viewed the page).  Thanks

Something like:

PagesWhoDatePage AadoirApril 25, 2023Page AfthiuApril 24, 2023Page BrtyuhFebruary 20, 2022Page ClkjgkiJanuary 10, 2020

Jacynthe_Thibault · Answer

@Clark Everson   Thanks  We tried it but the page_view was not find as table or view, it seems that it is not in the DB

Clark Everson_btzcub · Answer

Hi @Jacynthe_Thibault

Can you try in your staging instance

SELECT
cwd_user.user_name AS username,
 content.title AS page_title,
 content.creationdate AS page_creation_date,
 content.lastmoddate AS page_last_modified_date,
 page_view.date AS view_date
FROM
 content
 JOIN page_view ON content.contentid = page_view.contentid
 JOIN cwd_user ON page_view.username = cwd_user.user_name
WHERE
 content.contenttype = 'PAGE'
ORDER BY
 cwd_user.user_name,
 page_view.date;