Confluence Activity per Space

[Andre Lehmann]

Hi there,

our Confluence-Server is growing and growing with spaces...

Some projects requests a space but they never use that...
Some of them are aged out and left inactive.

Is there any way (database, macro, ?) that we can find out, when the last write-activity on every Confluence space was?

Basically I want one report on one page which indicates every space on our confluence and the last activity on that space.

We tried around with the Reporting-Plugin (space-reporter) which lists every Space, but the recently-Macro in that reports only shows activity of that space where the page is in, an not the one of the report.

Any suggestions, hints, ideas?

Kind regards
André

[Andre Lehmann · Accepted solution]

Hey Scott,

thanks for your answer.

As we are using Oracle this doesn't worked :D

But I showed that query to our DBA and he wrote a "small" query which exactly shows what I want:

select 
  max(c.lastmoddate), min(s.spacename)
from content c
  inner join spaces s
  on c.spaceid = s.spaceid 
group by c.spaceid

[Scott Dudley [Inactive]]

I'm glad you got it working! Unfortunately, Oracle is the only JIRA dB that does not have an easy way to do an ordered "limit" clause, so retrofitting the query in my answer (which also returns the page title and last-modifying user) would be tricky...but your dBA's query is a great way to get the space name and mod date only!

[Midori]

Hi Andre,

A simple "last page update date per space" may not be the most accurate metric, because in some spaces (or space categories) it may be acceptable that the space is not updated for say 3 months (e.g. quarterly reports), but in others, it means total inactivity (e.g. daily meetings).

The Archiving Plugin calculates a so-called "content quality" metric that primarily relies on the last update time (OK, this is a little more complicated), but which can be fine-tuned even per space. It is reported in a visual way, as below.

The nice thing that is recalculated periodically (configurable), and triggers multiple mechanism of the plugin automatically (like poking people to update or archive their abandoned pages, etc.).

Give it a try, even if you calculate it once and then completely uninstall the plugin, it should help you to see clearer.

[Nicolas Casel]

Hi all,

The Confluence documentation mentions:

The Activity tab does not appear in spaces that use the Documentation theme.

Any idea about the reason?

Thanks in advance,

[Scott Dudley [Inactive]]

Try this query. It's ugly, slow, and it probably includes comments and blog posts as well as pages in the calculation, but it should get you most of what you need. The syntax should be OK for everything except Oracle. Requires Confluence 5.3+.

select s.spacekey, 
(select title from content c where c.spaceid=s.spaceid order by lastmoddate desc limit 1) AS title,
(select um.username from content c left join user_mapping um on um.user_key=c.lastmodifier where c.spaceid=s.spaceid order by lastmoddate desc limit 1) AS modifier,
(select max(lastmoddate) from content c where c.spaceid=s.spaceid)
FROM spaces s
ORDER BY 1;

[Fabio Racobaldo _Herzum_]

Hello Andre,

there's a built-in macro "Recently Updated" that allows you to have information about recent activities on a single space.

You could use that in order to retrieve, per space, the informations you need.

More information about macro usage can be retrieved here https://confluence.atlassian.com/x/-yAC

Hope this helps,

Fabio

[Andre Lehmann]

Hi Fabio,

I know that Macro, but that only shows the recent activity either all or one space.
So if in space1 two pages were update, both are shown in the recently-Macro.

I need only the last activity for every space.

For example:
|| space || activity ||
| ABC | page xyz edited on foo by bar |
| BCE | page bla edited on fo by bar |
...

Sure I can create a table manually with every space and for every space I add the recently macro for that space.
But we have around 300 spaces...
Better would be an automated table/report wich generates the table with 300 rows (1 for each space) and also add the recently for that space in column1.

Regards
André

[Fabio Racobaldo _Herzum_]

Hi Andre,

so what do you need is a macro that display the, for each space, the last activity (page - operation type - user). Correct?

In order to have this feature in place, based on my experience, you could write your own macro. It's not too hard to do and there's this guide that could help you https://confluence.atlassian.com/display/DOC/Guide+to+User+Macro+Templates

Let me know if you need more help on that.

Regards,

Fabio

[Andre Lehmann]

Hi Fabio,

I'm not so familar with all those Atlassian classes and helpers to write a complete own UserMacro.
All I did yet was combining a set of Macros to one user-Macro :-)

But I will gibe this a try or better try to find a developer in our company :-)

Thanks for your answer.

Kind regards
André