Where does Confluence store its space views information ?

[Srikanth Kola]

I am trying to determine inactive spaces in Confluence. I dont want to query by last modified date. Since users can still use/view a space even though they are not making any updates to content.

So I decided to write a query by last viewed date using below query.

SELECT S.SPACENAME, V.[SPACE_KEY], MAX(V.LAST_VIEW_DATE) as LastViewDate
FROM [AO_92296B_AORECENTLY_VIEWED] V, [SPACES] S
WHERE (V.[SPACE_KEY] = S.[SPACEKEY] AND S.[SPACETYPE] = 'global')
GROUP BY S.SPACENAME, V.[SPACE_KEY] ORDER BY LastViewDate ASC;

But I have a feeling that [AO_92296B_AORECENTLY_VIEWED] is not the right table, because when I go to SPACES table it shows 289 global spaces. However when I use above query to figure our last viewed date for those spaces I only get 232 results.

Any advise is greatly appreciated. 

[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

[Srikanth Kola]

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

[Boris Berenberg - Atlas Authority]

I would just look at access logs, or consider using a third party app like https://marketplace.atlassian.com/plugins/com.addonengine.analytics/server/overview if this is a frequent review you want to perform / you have other analytics needs.

[Srikanth Kola]

I don't think we can buy a 3rd party plugin at this point of time.

And do you mean access logs on server ?

[Srikanth Kola]

Also I am talking about all the spaces that exist, not just a particular one

[Nic Brough -Adaptavist-]

Have you enabled the internal view tracking function (which is not recommended for medium to large Confluence systems)?

[Boris Berenberg - Atlas Authority]

Yes, I was talking about access logs on the server itself.
For context, what Nic is referencing is: https://confluence.atlassian.com/doc/viewing-site-statistics-123043962.html

[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