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