Sql query to find the below list

[Kotakonda, Bhargavi]

Im looking a sequel query in mysql to fetch the below listed feilds in confluence spaces and pages .

created by

last modified /last updated date

last viewed

number of views

incoming and outgoing links 

Thanks in Advance

[Humashankar VJ]

Also,

A few notes for anyone who needs or wants to dive deeper into SQL territory:

1. Always be extremely careful with any query you want to send to your database
2. If you are learning, do it in a testing environment. Never try new things on your production
3. Understand the database relationship
4. Check for similar queries
5. If you plan on changing something on your database, create a backup before
6. Updating the database requires Confluence to be down. Touching the database while Confluence runs will most likely break your instance

Some threads here in the community prove to be useful while learning:

• [Atlassian Community] Need a SQL statement to find a list of all spaces not updated in X amount of time and the corresponding space administrators
• [Atlassian Community] Confluence Query
• [Atlassian Community] SQL query to get all Confluence spaces with anonymous enabled

[Humashankar VJ]

Hi @Kotakonda, Bhargavi 

This query gives list of all spaces and associated users in every space

SELECT distinct s.SPACENAME, cu.lower_email_address
FROM SPACES AS s
JOIN SPACEPERMISSIONS AS sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping AS u ON sp.permusername = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE cu.lower_email_address is not null 
ORDER BY s.SPACENAME, cu.lower_email_address;

[Kotakonda, Bhargavi]

@Humashankar VJ this is not what i am looking for 

SELECT
COUNT(CONTENTID) AS "number of pages",
SPACES.SPACENAME,
MAX(CONTENT.LASTMODDATE) AS "LastModificationDate"
FROM CONTENT
JOIN SPACES ON CONTENT.SPACEID = SPACES.SPACEID
WHERE CONTENT.SPACEID IS NOT NULL
AND CONTENT.PREVVER IS NULL
AND CONTENT.CONTENTTYPE = 'PAGE'
AND CONTENT.CONTENT_STATUS = 'current'
GROUP BY SPACES.SPACENAME
ORDER BY "number of pages" DESC;

this is the query im using for now to fetch the space name number of pages and the last mod date but i want a  query which help me to fetch the child pages and the last mode 

[Kotakonda, Bhargavi]

And also add the creator and last modifier, also created date to it .

[Humashankar VJ]

Hi @Kotakonda, Bhargavi - Thanks for the clear level set on this - let me research and get you some outcome..

[Humashankar VJ]

Hi @Kotakonda, Bhargavi 

To fetch the child pages and their last modification dates, you can modify your query to use a recursive Common Table Expression (CTE)

Can you try the below one:
This joins the results with the spaces table to get the space name.
The outer query groups the results and fetches the count of pages, max last modification date, and a list of child page titles

WITH RECURSIVE page_cte AS (
SELECT CONTENTID, CONTENT.LASTMODDATE, SPACES.SPACENAME, CONTENT.TITLE
FROM CONTENT
JOIN SPACES ON CONTENT.SPACEID = SPACES.SPACEID
WHERE CONTENT.SPACEID IS NOT NULL AND CONTENT.PREVVER IS NULL AND CONTENT.CONTENTTYPE = 'PAGE'
UNION ALL
SELECT c.CONTENTID, c.LASTMODDATE, s.SPACENAME, c.TITLE
FROM CONTENT c
JOIN page_cte p ON c.PARENTID = p.CONTENTID
JOIN SPACES s ON c.SPACEID = s.SPACEID
)
SELECT SPACENAME, COUNT(CONTENTID) AS "number of pages", MAX(LASTMODDATE) AS "LastModificationDate", GROUP_CONCAT(TITLE, ', ') AS "Child Pages"
FROM page_cte
GROUP BY SPACENAME
ORDER BY "number of pages" DESC;

Warm Regards