Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,456,652
Community Members
 
Community Events
176
Community Groups

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

Rob Horan Community Leader May 20, 2021

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

1 answer

1 accepted

2 votes
Answer accepted

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 Community Leader May 20, 2021

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 Community Leader May 20, 2021

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

Phill

Like Rob Horan likes this
Rob Horan Community Leader May 20, 2021

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.

image (34).png

Phill Fox Community Leader May 20, 2021

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

Like Rob Horan likes this
Rob Horan Community Leader May 21, 2021

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.

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events