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

[mick davidson]

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.

[Saleh Parsa]

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

[mick davidson]

Saleh,

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

Cheers.

[Felipe Alencastro [Atlassian]]

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]

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

[Felipe Alencastro [Atlassian]]

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

[Felipe Alencastro [Atlassian]]

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

[Davin Studer]

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