I Need SQL query for run in JIRA 6.2 * List all the values in a custom field * To Update the email of a user

There's no confusion, you're trying to change email addresses on user accounts. It's not advisable to do it with SQL, but you can do it. I don't know why you keep asking about it, or why you've suddenly started looking at the login ids when you're only interested in email. Secondly, you specifically asked about custom fields. You've had an answer to that as well.

Hi Nic I think there may be some confusion here. The problem we have is that we’ve changed email address format for our users so for example where users used to be ihanif@londonstockexchange.com is now ihanif@lseg.com Consequently we need to update approx. 2000 user records and was wondering what would be the best method to achieve this We are not looking to update any custom fields If doing this via sql is not recommended then that's fine – we will look at alternative methods (any suggestions you have will be appreciated) Thanks kanchana

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL Help

kanchana wijerathna

I Need SQL query for run in JIRA 6.2

List all the values in a custom field
To Update the email of a user

Accepted answers

All comments

Nic Brough -Adaptavist-

Please avoid using the database, it's complex, messy and if you get it wrong, you can destroy your installation, and you will not technically be supported if anything goes wrong.

A far better option on the first one is really simple. Run and save a filter for "custom-field is not empty". Use that filter in a "filter statistics" gadget and group by the custom field. You'll get all the values, and a count of usage.

But.

You need to read customfieldvalue joined to customfield and jiraissue, if you want anything useful out of it.
It depends on your user directory. If it's internal, it's a couple of fields on cwd_user. You must shutdown JIRA completely if you insist on doing this with SQL, and restart when you've finished the update But if it's not internal, you'll need to look at whatever the user system is.

kanchana wijerathna

we have internal directory users and Active Directory users as well. we are using AD for authorization only, when we create users, we are manually creating users in the JIRA with proper directory. can you just show me the sql ?

Nic Brough -Adaptavist-

Nope, because you haven't really told me what data you think you want from the custom fields, so I'm guessing. And a filter is a far better, safer way to do it. SQL is a last resort and a terrible idea.

The SQL for the user changes is "update cwd_user with your changes". Remember to take a full backup before you run it, and have JIRA totally shut down when you do it.

kanchana wijerathna

The custom data issue is solved and now i want to update the email of set of users. i have set of users with xxx@abc.com and want to change those to xxx@pqr.com. Is there any sq to do this ? bulk change ?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-administration

jira-core