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

[Karie Kelly]

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. 

[Rodrigo Girardi Adami · Accepted solution]

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

[Karie Kelly]

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

[Stephen Deutsch]

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 

[Karie Kelly]

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!