Syntax errors generating CSVs for user and group import during cloud migration

[Rob Horan]

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'.

[Phill Fox · Accepted solution]

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

[Rob Horan]

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?

[Phill Fox]

I had accidentally hit publish my answer before finishing. Have now added on the bit about cwd_user.  

Phill

[Rob Horan]

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.

[Phill Fox]

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

[Rob Horan]

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.