Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

1 vote
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

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?

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

Phill

Like Rob Horan likes this

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

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

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
Community showcase
Published in Atlassian Cloud Migration

Cloud migration resources roundup: May 2021

...est practices.   And don’t forget about the tried and true! Cloud Migration Center - central hub for all things Atlassian Migration Program. Includes links to our full resources page, s...

558 views 2 5
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you