Finding inactive spaces in Confluence

[Srikanth Kola]

I found the below articles where you can find inactive spaces by last modified date. However a user may still use a spaces (just viewing) with out updating any contend. Any ideas on how to write a query with "last viewed date" ?

[Srikanth Kola · Accepted solution]

Select * from [Confluence].[dbo].[SPACES] where [SPACEKEY] not in (SELECT V.[SPACE_KEY] FROM [Confluence].[dbo].[AO_92296B_AORECENTLY_VIEWED] V, [Confluence].[dbo].[SPACES] S WHERE (V.[SPACE_KEY] = S.[SPACEKEY] AND S.[SPACETYPE] = 'global') GROUP BY V.[SPACE_KEY]) AND [SPACETYPE] = 'global' order by [LASTMODDATE] ASC

[Mason Crouse]

Hi Srikanth, probably a stupid question, but where do I run this query?

[Tran Tien dung]

The query above is incorrect. See this link for solution

[Julia Nunes Pedrosa]

Hey Srikanth, can you help me? What version of Confluence are you using??

[AnnWorley]

I do not think the database stores a record for each time a page is viewed, however, there are other options described on this page: How to Enable User Access Logging

Other than access logging the article has options for Google Analytics and the Tomcat Valve Component access log.

I look forward to hearing whether any of these work for your case.

[Srikanth Kola]

When you go to Activity option in any space in Confluence you can see Activity of Viewing. Doesn't mean its being stored somewhere ?

[Nic Brough -Adaptavist-]

Yes, in a cache, which goes away after a while.

[AnnWorley]

The activity stream depends on the content index on the file system: Content Index Administration:

The content index, also called the search index, supports Confluence's search functionality. It is also used for a number of related functions such as building email threads in the mail archive, the space activity feature, and lists of recently-updated content

Also, the activity stream shows changes and not views.

[Srikanth Kola]

I user below query to get the results, thanks for you assistance

Select * from [Confluence].[dbo].[SPACES] where [SPACEKEY] not in (SELECT V.[SPACE_KEY] FROM [Confluence].[dbo].[AO_92296B_AORECENTLY_VIEWED] V, [Confluence].[dbo].[SPACES] S WHERE (V.[SPACE_KEY] = S.[SPACEKEY] AND S.[SPACETYPE] = 'global') GROUP BY V.[SPACE_KEY]) AND [SPACETYPE] = 'global' order by [LASTMODDATE] ASC