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
Hi @Kotakonda_ Bhargavi
This query gives list of all spaces and associated users in every space
SELECT distinct s.SPACENAME, cu.lower_email_addressFROM SPACES AS sJOIN SPACEPERMISSIONS AS sp ON s.spaceid = sp.spaceidLEFT JOIN user_mapping AS u ON sp.permusername = u.user_keyLEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_usernameLEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_nameLEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_idLEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_idWHERE cu.lower_email_address is not null ORDER BY s.SPACENAME, cu.lower_email_address;
Also,
A few notes for anyone who needs or wants to dive deeper into SQL territory:
Always be extremely careful with any query you want to send to your database If you are learning, do it in a testing environment. Never try new things on your production Understand the database relationship Check for similar queries If you plan on changing something on your database, create a backup before 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 this is not what i am looking for
SELECTCOUNT(CONTENTID) AS "number of pages",SPACES.SPACENAME,MAX(CONTENT.LASTMODDATE) AS "LastModificationDate"FROM CONTENTJOIN SPACES ON CONTENT.SPACEID = SPACES.SPACEIDWHERE CONTENT.SPACEID IS NOT NULLAND CONTENT.PREVVER IS NULLAND CONTENT.CONTENTTYPE = 'PAGE'AND CONTENT.CONTENT_STATUS = 'current'GROUP BY SPACES.SPACENAMEORDER 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
And also add the creator and last modifier, also created date to it .
Hi @Kotakonda_ Bhargavi - Thanks for the clear level set on this - let me research and get you some outcome..
It looks like you're new here. Sign in or register to get started.
