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

[Kamil Beer]

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!

[Nic Brough -Adaptavist- · Accepted solution]

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.

[Kamil Beer]

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.

[Nic Brough -Adaptavist-]

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?

[Kamil Beer]

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

[Nic Brough -Adaptavist-]

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.

[Kamil Beer]

Thanks. Do you recommend any particular queries?