Is there any provision to get the Last Viewed Space details of a space from confluence?

[Mohammed Siyad]

Hi team,

I am just looking for query in SQL or a method in groovy to get the details of last viewed dates of each item in confluence.

SELECT SPACE_KEY, LAST_VIEW_DATE from "AO_92296B_AORECENTLY_VIEWED"

I found out this query would help, but its not getting the actual stats(I mean the entire space details)

Appreciating any help!!

[Mehmet Şirin Usanmaz]

Hi @Mohammed Siyad ,

You can get a small report with the following sql query. You can check the tables used for detailing.

SELECT c.title, av."SPACE_KEY", u.username AS Viewer, av."LAST_VIEW_DATE"
FROM content c 
JOIN "AO_92296B_AORECENTLY_VIEWED" av ON c.contentid=av."CONTENT_ID"
JOIN user_mapping u ON av."USER_KEY" = u.user_key 
JOIN user_mapping um ON c.lastmodifier = um.user_key 
WHERE prevver IS NULL 
AND contenttype = 'PAGE' 
ORDER BY title; 

[Mohammed Siyad]

Will this get the data of the entire space thats there in confluence because I am looking for a data which gives like this : @Mehmet Şirin Usanmaz 

Space Key/Space Name	Last Viewed Date
ABC	Date
FGH	Date

[Mehmet Şirin Usanmaz]

Hi @Mohammed Siyad ,

You can use this query

SELECT DISTINCT "SPACE_KEY" as "Space Key", MAX("LAST_VIEW_DATE") as "Last Viewed Date"
FROM "AO_92296B_AORECENTLY_VIEWED"
GROUP BY "SPACE_KEY"

[Mohammed Siyad]

Hi @Mehmet Şirin Usanmaz ,

Still its not retreiving the entire space Last Viewed Details stats. I am in a way to setup a report which has list of active users in a space and Last Viewed Date of a space.

[Mohammed Siyad]

Hi @Mehmet Şirin Usanmaz ,

Tried the query but it gives the data of past 1 month only, i do have like around 100+ spaces. Is there any possibility like we can get it for past 1year.