How do I search Confluence for pages that were last modified by a user?

How do I search for confluence pages that were last modified by a user or last modified in the last 2 years? 

Also, how do I search for confluence pages that were created or last modified by a user who is no longer active in the system?  They left the company, but we need to find something that they worked on.  It seems that the advanced search Author option is based only on users - which, this person is no longer an active user and not appearing as an option. 

2 answers

1 accepted

This widget could not be displayed.

Hi Karie,

You can do that using SQL queries.

For example to search for pages which were last modified by a specific user. Just change the 'user_name' for the user you want to search.

SELECT c.title, u.lower_username, c.lastmoddate FROM content c
join user_mapping u on u.user_key=c.lastmodifier
where u.lower_username = 'user_name'
group by c.title;

For pages which where modified in the last 2 years. Just change the lastmoddate value for the date you would liek to check.

SELECT c.title, u.lower_username, c.lastmoddate FROM content c
join user_mapping u on u.user_key=c.lastmodifier
where c.lastmoddate > '2013-02-19 12:00:00'
group by c.title;

To search for pages from an user who is no longer part of the system, just place the username in the first query and you'll see the contents created by him.

Cheers,

Rodrigo

Unfortunately, I don't have direct access to the db and needed to use a solution that allowed me to get the information through the UI; but, I'm sure this will help someone who does have access to the db

This widget could not be displayed.
Stephen Deutsch Community Champion Feb 19, 2015

Hi Karie, it seems like you already know how to search for pages that were created/modified by a user: you can simply search in the sidebar.

In order to search for someone who is no longer active in the system, it is a little more difficult, but not impossible.  I am assuming you are running Confluence 5.3 or later: you will need to find the user key for the user.  This can be done one of two ways.  If you know a page where the user is either the creator or last modifier, you can run the following user macro:

## @noparams
<p>$content.creator.key</p>
<p>$content.lastModifier.key</p>

You will then need to go to the page, edit, insert the user macro, and click "preview". The first line is the key for the user who is the creator, and the second line is the key for the user who is the last modifier.

An alternative option is to find a page which you know the user has edited and export the page using XML (using the custom export and selecting only the 1 page). Then unzip the saved file, edit the XML using a text editor, and search for the user's username, and somewhere in the vicinity you will be able to see the user's user key.

Once you have the user's key, all you need to do is search for "lastModifiers:123abc" where 123abc is the user's user key.  This will find all pages that the user has ever created or edited.  If you want to further restrict the pages to those from the last two years, just add the following to your search: "AND modified:[20130219 TO 20150219]".  If you want to restrict the results with more search terms just add "AND searchTerm" where searchTerm is the word you are looking for.

If you have access to the SQL database, then it might just be easier to use Rodrigo's answer above smile

Thank you for the help as I needed a solution that I could get to through the UI as I don't have direct access to the db. I was able to use the last modifier search to locate the pages. Do you happen to know how to do the same thing, but for creator. I tried creators, creator,created, createdby, author without any luck. Since I cannot use the author field since the user is not active, I was wondering if I could still find the pages using this same method. Thanks!

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Sep 17, 2018 in Confluence

Why start from scratch? Introducing four new templates for Confluence Cloud

Hi my Community friends!  For those who don't know me, I'm a product marketer on the Confluence Cloud team - nice to meet you! For those of you who do, you know that I've been all up in your Co...

569 views 7 6
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