Database query for user's history

[Kristin Lyons]

We are in the process of merging two Jira instances together.  The instances have different user bases.  We would like to find a database query that will list all of the active users who have data associated with their account (issues assigned to them, issues they created, issues they are the reporter on, issues they commented on, etc).  Is there any way to do this?

My use case: We want to create users in the the final Jira instance only if they are currently active in the other Jira instance and only if they have data associated with their account.  In order to do this we need to compile a list of those users.  We are limited by our Jira software server license tier at this point and are not planning on migrating environments until next year.

I have come up with the following queries below but I'm not sure how I can consolidate them and also a way to get any other information that we might need (such as the comments).

I know I can query:

select * from cwd_user where active = 1;

select creator from jiraissue order by creator;

select reporter from jiraissue order by reporter;

select assignee from jiraissue order by assignee;

[Nic Brough -Adaptavist- · Accepted solution]

You'll need to read a lot more tables than those.   Comments, issue history, (multi-)user pickers, and and and.

A far more simple solution - go to the list of users and deactivate any user who has not logged in within a reasonable length of time.   That way you won't lose data or have to migrate it all when you drop them during migration, but they won't count towards your licence limits.