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

This widget could not be displayed.

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.

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 ?

 

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.

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 ?

You have already been told the answer to that.

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 ?

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.

So updating EMAIL_ADDRESS field is enough ?

You already have the answer to that.

 

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

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.

Ok thank you for helping us

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted 2 hours ago in Jira

Atlassian Research Workshop opportunity on Sep. 28th in Austin, TX

We're looking for participants for a workshop at Atlassian! We need Jira admins who have interesting custom workflows, issue views, or boards. Think you have a story to sha...

14 views 0 0
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you