SQL Help

kanchana wijerathna February 1, 2016

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

1 answer

1 vote
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 1, 2016

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 February 1, 2016

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 1, 2016

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 February 1, 2016

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 1, 2016

You have already been told the answer to that.

JIRA Team Mailbox February 10, 2016

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 10, 2016

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 February 10, 2016

So updating EMAIL_ADDRESS field is enough ?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 10, 2016

You already have the answer to that.

 

kanchana wijerathna February 10, 2016

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-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 11, 2016

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 February 11, 2016

Ok thank you for helping us

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events