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

How to I search for all occurences of a username in Jira's database?

Hello,

when removing a user, I would like to search for all occurences where that user was used in Jira: In workflows, permission schemes, notification schemes. I thought a good idea could be searching the whole database, but I can't seem to find or figure out the right query to do so.

Thanks!

1 answer

1 accepted

1 vote
Answer accepted

That's a horrible way to do this.  You'll need to join the name to the ID, then join that on to around 20 different tables, many of which will need another join to work out what it's for, and others will need you to fully understand the code behind the workflow so you can parse the data from the query accurately.  And there is no way you should be using such a query to update anything as this level of complexity guarantees you will get it wrong and cripple the system.

A far better approach would be to use the UI to delete the user.   This is still going to give you problems that you won't enjoy, but at least is supported.

The best approach is to simply disable the account and forget it.

Heh, never said anything about updating it there :) My goal is to find how often and where are these users used. Deleting a user is also not an option, since I want to keep all of his comments, for example.

So while it might be a horrible way, it's something I want to explore. If there is a way to easily find ex. all workflows that mention a user (in a condition, validator or pf), I am interested.

Yep, you said nothing about updates.   My "don't use SQL with Jira" reaction nowadays is as almost instinctive as "I feel thirsty, find a drink".  So I always say it!

"Deleting a user is not an option" - fantastic, I wish more people understood that!

On the down-side, there's no easy way to find where an account might have been used.  There are some clumsy methods (searching the raw xml the backup generates, searching 60%-ish of the tables, etc) but they're never going to be complete.

As this looks like quite a complex question, I would head back to refine the real question.  Why do you want to do this?  I am imagining a simple case where "Dave joins company in 2015, Dave does lots of work, Dave retires in 2019 and leaves some stories incomplete".   If you just disable the account, Dave can't be assigned, selected, mentioned etc, but you have a complete accurate record of his work.  Why do you want to delete him, rather then just disable?

I heard about all the complex links in Jira db, so I would definitely delete just from the application.

One of the use cases would be that I am performing a workflow cleanup and there are some workflows in which Dave was included in a validator or in a condition. These workflows would show errors when the user is missing. Or perhaps he was added to a project role in various projects and I simply want to clean up (similar with Permission/Notification schemes).

Ok, narrowing it down to workflow does make it more simple, but it is still unpleasant for us humans, as the workflows are a block of xml

You'll need to read cwd_user to get the id for the account, then search every line in the jiraworkflows table for that id, making sure you check that the id refers to a user and not something else.

Thanks. Do you recommend any particular queries?

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Management

JSM June ask me anything (AMA)

Hello Community members! We’re wrapping up the end of JSM June with an Ask Me Anything (AMA) with the Jira Service Management product team. This is your chance to ask all your ITSM questions to o...

82 views 3 8
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