SQL Query for finding pages not edited from before a spedific date

Hello,

We've got Pocket Query installed in our Confluence (5.4.3 download) and I want to use it to create a SQL query that does the following:

  • find pages in a specific space; that are sub-pages of page X; that were last edited before date X.

Whilst I can read SQL to a degree, I don't write it, so do any of you wonderful people have such a thing already written?

Thanks in advance.

3 answers

Hi Mick,

I think it's what you are look after:

select title from content where (lastmoddate >= 'YYYY-MM-DD' AND contenttype = 'ParentPageName') AND spaceid = (select spaceid from spaces where spacename ='YourSpaceName');

Let me know if I've misunderstood on what you're looking to achieve.

Cheers,

Saleh

Saleh,

Thanks for your answer, I will see if I can make it work and get back to you. :)

Cheers.

Hi Mick,

I made some modifications to the query provided by my colleague, that I believe will work for you case:

SELECT title
FROM content
WHERE (lastmoddate >= 'YYYY-MM-DD'
       AND contenttype = 'PAGE'
       AND content_status = 'current'
       AND prevver IS NULL
       AND parentid IN 
		(WITH RECURSIVE fcontent (contentid) AS
                (SELECT contentid
                FROM content
                WHERE title = 'ParentPageName'
                	AND prevver IS NULL
                     	AND content_status='current'
                UNION ALL SELECT c2.contentid
                FROM content c2
                INNER JOIN fcontent ON 
			fcontent.contentid = c2.parentid)
                SELECT *
                FROM fcontent))
  	AND spaceid =
		(SELECT spaceid
     		FROM spaces
     		WHERE spacename ='YourSpaceName');

Best regards,

Felipe Alencastro

Davin Studer Community Champion Jun 03, 2014

One thing to note this would only get direct children ... not grandchildren and so forth.

Hi Davin,

You're right, sorry for that. Here's the new query that will also return grandchildren pages and so:

SELECT title
FROM content
WHERE (lastmoddate >= 'YYYY-MM-DD'
       AND contenttype = 'PAGE'
       AND content_status = 'current'
       AND prevver IS NULL
       AND parentid IN (WITH RECURSIVE fcontent (contentid) AS
                          ( SELECT contentid
                           FROM content
                           WHERE title = 'ParentPageName'
                             AND prevver IS NULL
                             AND content_status='current'
                           UNION ALL SELECT c2.contentid
                           FROM content c2
                           INNER JOIN fcontent ON fcontent.contentid = c2.parentid)
                        SELECT *
                        FROM fcontent))
  AND spaceid =
    (SELECT spaceid
     FROM spaces
     WHERE spacename ='YourSpaceName');

Best regards,

Felipe Alencastro

Hi Davin,

Yes you're right. I'm sorry for that. I edited the query again to include grandchildren and so.

Best regards,

Felipe Alencastro

0 vote
Davin Studer Community Champion Jun 03, 2014

Just to throw it out there you could also do this using a user macro that implements the search macro. Here is what I created for my organization.

Macro Name:
stalepages

Macro Title:
Stale Pages

Macro Body Processing:
No Macro Body

Template:
## Developed by: Davin Studer
## Date created: 12/26/2013
## This will show pages that have not been modified within the last (x) number of days.

## @paramDaysBack:title=Not Modified Within Last (x) Days|type=int|required=true|desc=Pages that have not been modified within the given amount of days.
## @paramSpaceKey:title=Restrict to this Space Key|type=string|required=false|desc=This is case-sensitive. If not specified, all accessible spaces are searched.
## @paramParent:title=Parent Page|type=confluence-content|required=false|desc=This will restrict the results to only pages that are children of this parent page.

#if($paramDaysBack && $paramDaysBack != "")
##########################################################
## Convert the parameter to an integer. Even though the ##
## param type is int Confluence still treats as a ##
## string. ##
##########################################################
#set($paramDaysBack = $generalUtil.convertToInteger("-$paramDaysBack"))

##########################################################
## Take today's date and subtract the days back number. ##
##########################################################
#set($cal = $action.dateFormatter.getCalendar())
$cal.add(5, $paramDaysBack)
#set($end = $action.dateFormatter.formatGivenString("yyyyMMdd", $cal.getTime()))

##########################################################
## Get space key to use ##
##########################################################
#if($paramSpaceKey && $paramSpaceKey != "")
#set($key = $paramSpaceKey)
#else
#set($key = $content.spaceKey)
#end

##########################################################
## Get id of Parent page ##
##########################################################
#if($paramParent && $paramParent != "")
#set($containerManagerClass=$content.class.forName('com.atlassian.spring.container.ContainerManager'))
#set($getInstanceMethod=$containerManagerClass.getDeclaredMethod('getInstance',null))
#set($containerManager=$getInstanceMethod.invoke(null,null))
#set($containerContext=$containerManager.containerContext)
#set($pageManager=$containerContext.getComponent('pageManager'))

#if($pageManager)
#set($tempcontent = $pageManager.getPage($key, $paramParent))
#end
#if($tempcontent && $tempcontent != "")
#set($content = $tempcontent)
#set($id = "AND ancestorIds:$content.getIdAsString()")
#else
The page "$paramParent" was not found in the $key space.
#set($content = "")
#end
#end

#if($content != "")
##########################################################
## Render the search macro with 19000101 as the start ##
## date and our calculated back days as the end. ##
##########################################################
<ac:structured-macro ac:name="search">
<ac:parameter ac:name="query">modified:[19000101 TO
$end]$id</ac:parameter>
<ac:parameter ac:name="maxLimit">999999999</ac:parameter>
<ac:parameter ac:name="type">page</ac:parameter>
<ac:parameter ac:name="spacekey">
<ri:space ri:space-key="$key"/>
</ac:parameter>
</ac:structured-macro>
#end
#end

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Kesha Thillainayagam
Posted Apr 13, 2018 in Confluence

We want to hear how your non-technical teams are using Confluence!

Hi Community! Kesha (kay-sha) from the Confluence marketing team here! Can you share stories with us on how your non-technical (think Marketing, Sales, HR, legal, etc.) teams are using Confluen...

2,901 views 27 12
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