This page last changed on Nov 20, 2007 by jfleming.

This page has been moved to the Administration section, and will be removed from here at some indeterminate point in the future.

Sometimes, you want to know what is not being used. It's great to know what's getting most attention, but what about stagnant pages, or even entire spaces that are no longer active?

While viewing space activity and the Global Statistics plugin can provide hints, they still doesn't always provide enough detail. The simple way is to go directly to the database. We recommend DbVisualizer, and have basic instructions for connecting it to HSQLDB.

The following query identifies the last date on which content was modified in each space within a single Confluence instance:

SELECT spaces.spacename, MAX(content.lastmoddate)
FROM content, spaces
WHERE content.spaceid = spaces.spaceid
GROUP BY spaces.spacename;

It returns a list of spacenames, and the last date and time at which any content was added or changed.

Alternatively, this one simply identifies spaces whose content hasn't changed since a specified date:

SELECT spaces.spacename
FROM content, spaces
WHERE content.spaceid = spaces.spaceid
GROUP BY spaces.spacename
HAVING MAX(content.lastmoddate) < '2006-10-10';

The result is a simple list of space names.

It's also possible to present the information in a wiki page, using the SQL plugin, which can be installed via the Plugin Repository. You'll also need to define a database Resource in conf/server.xml and confluence/WEB-INF/web.xml, as described here. Having done so, you can use wiki markup code like the following, replacing confluenceDS with the name of your own local datasource:

h3. Space activity
{sql:dataSource=confluenceDS|output=wiki}
SELECT spaces.spacename AS Space, MAX(content.lastmoddate) AS LastModified
FROM content, spaces
WHERE content.spaceid = spaces.spaceid
GROUP BY Space;
{sql}

!DOC:How do I find unused spaces?^SQLscreenshot.png!

The Chart plugin can be used in combination with the SQL plugin to give more visually attractive results.


SQLscreenshot.png (image/png)
Document generated by Confluence on Aug 07, 2008 19:08