nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Accepted answers

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

All comments

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)?

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