Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

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 Marketplace Apps & Integrations

Bitbucket Smart Commits vs. Genius Commits - What's the difference?

If you already heard about Smart Commits in Bitbucket, know that you just stumbled upon something even better (and smarter!): Genius Commits by Better DevOps Automation for Jira Data Center (+ Server...

130 views 0 2
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