Confluence user resources tracking

Question

I am tryingt to track all resources tied to a user. currently i have a sql select query i'm trying to use to find all resourses for "user". will this show me all resources or do i need to join to other tables to get more items. If i create a page in confluence i would like a way of tracking everything associated with that page. is this query the best way to achieve that end?

thank you

select contentid,contenttype,creationdate,versioncomment,content_status,username
from content

where creator = 'user'

order by contentid

Sean King1 · Answer

Not sure if this is too simple of a solution, but you could use the "Content by User" macro on a page to get all the pages, comments, and blog posts from a specific user. Doesn't do attachments however.

Adam Laskowski · Answer

Each time you edit a page, a new version is created in the content table, so if you see a page with the same TITLE and SPACEID, they are different versions of the same page.  Editing a page will not delete older versions as Confluence allows you to revert to any historic version of the page at will.

A cleaner way to display the information you want is to exclude the historical versions of the page.  All current versions of a page will have a null value for the PREVVER column, so you can use something like this query to pull that information:

select c.title, c.contentid, c.contenttype, c.creationdate, c.content_status, c.spaceid from content c join (select title, spaceid from content where creator='user' and prevver is null and contenttype not in ('SPACEDESCRIPTION', 'GLOBALDESCRIPTION', 'DRAFT') group by spaceid, title) t on c.title=t.title and c.spaceid=t.spaceid and c.prevver is null;

You can get a count of historical versions by contentid for pages with >1 version in that above query with this:

select prevver, max(version) from content where prevver in (select c.contentid from content c join (select title, spaceid from content where creator='user' and prevver is null and contenttype not in ('SPACEDESCRIPTION', 'GLOBALDESCRIPTION', 'DRAFT') group by spaceid, title) t on c.title=t.title and c.spaceid=t.spaceid and c.prevver is null) group by prevver;

I was trying to find a better way to aggregate both the list of current, unique content and a count of the historical versions, but I think I blew a fuse in my head pondering the logic of such a thing.

Side notes for the columns you're selecting: CONTENT_STATUS will be 'current' for any piece of content that hasn't been deleted and not emptied from the space's trash.  This mostly just applies to pages and comments, if memory serves.  Also, username is normally blank, except for a few resouces, such as the 'SPACEDESCRIPTION' CONTENTTYPE for the user's personal space.

Jeff Underwood · Answer

Rodrigo,

I think what is throwing me off with this is i have several pages with the same title, each with the same CREATIONDATE but each with different LASTMODDATE and different CONTENTID. So, i am not sure if i'm looking at the same object because i only have one page with with this particular title. Do each page have their own resources? i'm curious why it's there 5 different times.

I think at this point i need to understand what happens when i create one page without updating it. does it create a new page everytime i update, without deleting the previous page.