You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
Next: Root
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
The Atlassian Community can help you and your team get more value out of Atlassian products and practices.
Per https://support.atlassian.com/migration/docs/determine-your-user-migration-strategy/ I need to run the following SQL queries from the Jira Server and Confluence database to create CSV files of users and groups:
Users:
SELECT email_address AS "email", user_name AS "username", display_name AS "displayname", active AS "active" FROM cwd_user |
Groups:
SELECT DISTINCT parent_name AS group, child_name AS username FROM cwd_membership; |
When the groups query is run (MS SQL Server 2016) I get an error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'group'.
Any ideas?
Also, i seen to be getting another error on one particular server when running the Users query:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'cwd_user'.
Hi @Rob Horan
The reason for failure is that Group is a reserved word for SQL.
So try this version
SELECT DISTINCT parent_name AS jiragroups, child_name AS username FROM cwd_membership;
With regard to the cwd_user this sounds like either a permission issue with the table or possibly that the table is missing which would be very unlikely if the solution is still running.
I would start troubleshooting with checking the permissions on the table. If it is absent then you will know why you cannot include it in a query.
Hope this helps.
Phill
WOW - ok, so this worked in Dev, will check to see if this works in Prod.
Thanks @Phill Fox !
You wouldn't happen to know why I am also getting the Invalid object name 'cwd_user' error on the other query in Confluence by chance?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I had accidentally hit publish my answer before finishing. Have now added on the bit about cwd_user.
Phill
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks @Phill Fox
You were right about permissions. Now oddly I am able to get info from Jira, but am unable to run the group query on Confluence:
SELECT DISTINCT parent_name AS groupname, child_name AS username FROM cwd_membership;
Msg 207, Level 16, State 1, Line 1
Invalid column name 'parent_name'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'child_name'.
Looking at dbo.cwd_membership, I don't see those column names... so in that sense the error makes sense, but then Atlassian's documentation doesn't.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Rob Horan
Ah yes the delights of the confluence database structure.
If you have not found it already the page https://confluence.atlassian.com/doc/confluence-data-model-127369837.html is super useful especially if you match it to your version of Confluence.
Using the SQL on https://support.atlassian.com/migration/docs/confluence-pre-migration-checklist/ for checking the user cloud limit as a starting point
SELECT u.lower_user_name, d.directory_name, g.group_name FROM cwd_user u JOIN cwd_membership m ON u.id = child_user_id JOIN cwd_group g ON m.parent_id = g.id JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME JOIN cwd_directory d on u.directory_id = d.id WHERE sp.PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T' GROUP BY u.lower_user_name, d.directory_name ORDER BY d.directory_name;
You should be able to manipulate this SQL to get the information you require from Confluence.
Sorry but I am not currently in front of an instance to check it all completely for you.
Phill
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Nothing to be sorry about, I'm incredibly grateful for all your help, thank you! I'd buy you a pint on the spot if I could.
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.