Im trying create a report that pulls out all pages not updated in the last month and I'm looking to try and show All Parents ( if any) of those pages.
Anyone out there who has tried this and suceeded?
Thanks
I have a SQL query from a few years ago that found all descendants, so I imagine the same approach would work for finding ancestors/parents. You would also need to add a WHERE clause that looks for lastmoddate being outside the last 30 days.
-- Set variable representing the page whose descendants you want to find DECLARE @contentid NUMERIC(19, 0) SET @contentid = 65106350 -- Declare table to collect all descendants under a particular page DECLARE @descendants TABLE ( contentid NUMERIC(19, 0) NOT NULL PRIMARY KEY, tltitle VARCHAR(255) NULL, lltitle VARCHAR(255) NULL ) -- Load @Descendants table with direct (i.e. top-level) descendants INSERT @descendants ( contentid, tltitle, lltitle ) SELECT C.contentid, C.title, C.title FROM content C WHERE parentid = @contentid -- Load @Descendants table with indirect (lower-level) descendants, ensuring -- no duplicates WHILE @@rowcount <> 0 BEGIN INSERT @descendants ( contentid, tltitle, lltitle ) SELECT C.contentid, D.tltitle, C.title FROM @descendants D INNER JOIN content C ON C.parentid = D.contentid WHERE NOT EXISTS(SELECT 1 FROM @descendants D2 WHERE D2.contentid = C.contentid) END -- Show results (abbreviated) SELECT D.tltitle AS ParentPage, D.lltitle AS PageTitle, 'http://wikiurl.com/pages/viewpage.action?pageId=' + CONVERT(VARCHAR(10),D.contentid) AS ViewPageURL, 'http://wikiurl.com/pages/editpage.action?pageId=' + CONVERT(VARCHAR(10),D.contentid) AS EditPageURL FROM @descendants D ORDER BY D.tltitle, D.lltitle
You can use this plugin, but it will only show page views from the day the plugin is added: https://marketplace.atlassian.com/plugins/net.customware.confluence.plugin.tracking/server/overview
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.