Confluence user resources tracking

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

3 answers

0 vote

Hi Jeff,

In a query for a page you can track the child pages and the space from where it was created, but depending on what you want to track, that could not be the best way to achieve.

For example, if you want to track the macros from inside a page, you may want to do a select query from the bodycontent table that is related to the content id from the page and specify the macro in the query, however this is specific for macro tracking. Other tracking will need to be tweaked in order to work. It would be a bit laborious to accomplish.

Hope that helps. I'll leave this question open so others can try to help you as well.

Cheers,

Rodrigo

thanks for the assistance Rodrigo.

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.

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.

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.

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Posted Jul 10, 2018 in Confluence

We want to see the templates you've created in Confluence!

Hi Community, Jessica here from the Confluence Product Marketing team!  July’s community challenge is all about sharing pictures  — and as an extension of our first post on what ...

628 views 21 11
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you