Confluence Site History Data

[Nigel Cheetham]

Is it possible to obtain, through either a report or a query, Spaces history for our Confluence site. For example, can I get a report that would show me, for each personal or team space:

• When the Space was created, and who created it?
• When a page was added to the space, by whom, and the name of the page?
• A history of which users were given access to the space?

We don't need to see the audit of the changes made to pages within a Space, just the space history itself?

Thanks.

[Tobias Anstett _K15t_ · Accepted solution]

Hi Nigel,

If you want to report on this I propose to use SQL queries.

In general the data you want to report on is available as there are UIs for it in Confluence. E.g. space overview, first revision of pages in the page history as well as the global audit log.

However the report is limited to what is stored in the DB. So if pages are deleted and purged they won't show up in your reports.

The following queries were done for PostgreSQL and one of the latest Confluence versions. Depending on your DB and the Confluence version used, changes might be required.

1) Query to list all spaces with name, key, creator and creation date

select A.spacename, A.spacekey, A.creationdate, B.lower_username, C.display_name
from spaces as A, user_mapping as B, cwd_user as C
where A.creator = B.user_key and B.lower_username = C.lower_user_name

2) Query to list initial page versions with creator for all spaces. You can narrow it down by asking for a specific space key.

select D.spacekey, D.spacename, A.lowertitle, B.lower_username, C.display_name
from content as A, user_mapping as B, cwd_user as C, spaces as D
where A.contenttype = 'PAGE' and A.version= 1 and A.content_status = 'current' and A.creator = B.user_key and B.lower_username = C.lower_user_name and A.spaceid = D.spaceid

3) Query to list all permissions added and removed events for space permissions

select A.summary, A.authorname, A.authorfullname, A.objectname, A.creationdate, B.name from auditrecord as A, audit_affected_object as B
where A.auditrecordid = B.auditrecordid and category = 'Permissions' and B.type = 'Space'

Hope this helps :)

Best, Tobias

[Tobias Anstett _K15t_]

Hi Nigel,

If you want to report on this I propose to use SQL queries.

In general the data you want to report on is available as there are UIs for it in Confluence. E.g. space overview, first revision of pages in the page history as well as the global audit log.

However the report is limited to what is stored in the DB. So if pages are deleted and purged they won't show up in your reports.

The following queries were done for PostgreSQL and one of the latest Confluence versions. Depending on your DB and the Confluence version used, changes might be required.

1) Query to list all spaces with name, key, creator and creation date

select A.spacename, A.spacekey, A.creationdate, B.lower_username, C.display_name
from spaces as A, user_mapping as B, cwd_user as C
where A.creator = B.user_key and B.lower_username = C.lower_user_name

2) Query to list initial page versions with creator for all spaces. You can narrow it down by asking for a specific space key.

select D.spacekey, D.spacename, A.lowertitle, B.lower_username, C.display_name
from content as A, user_mapping as B, cwd_user as C, spaces as D
where A.contenttype = 'PAGE' and A.version= 1 and A.content_status = 'current' and A.creator = B.user_key and B.lower_username = C.lower_user_name and A.spaceid = D.spaceid

3) Query to list all permissions added and removed events for space permissions

select A.summary, A.authorname, A.authorfullname, A.objectname, A.creationdate, B.name from auditrecord as A, audit_affected_object as B
where A.auditrecordid = B.auditrecordid and category = 'Permissions' and B.type = 'Space'

Hope this helps :)

Best, Tobias