SQL Help

[kanchana wijerathna]

I Need SQL query for run in JIRA 6.2

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

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

1.  You need to read customfieldvalue joined to customfield and jiraissue, if you want anything useful out of it.
2. 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 ?

[Nic Brough -Adaptavist-]

You have already been told the answer to that.

[JIRA Team Mailbox]

Is this corerct ?

UPDATE  CWD_USER SET LOWER_EMAIL_ADDRESS = "XXX" WHERE LOWER_USER_NAME = "user_name";

What is the difference between LOWER_USER_NAME and USER_NAME ?

[Nic Brough -Adaptavist-]

They're for handling external systems which allow for mixed case alongside ones that do not.

Do not touch those fields, you don't need to.

[kanchana wijerathna]

So updating EMAIL_ADDRESS field is enough ?

[Nic Brough -Adaptavist-]

You already have the answer to that.

[kanchana wijerathna]

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

[Nic Brough -Adaptavist-]

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.

[kanchana wijerathna]

Ok thank you for helping us