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

Why do project leads show up as IDxxxxxx in postgreSQL project table

I'm trying to get a list of all my project leads by querying my postgreSQL database directly. However, when I run either of the following queries, I only get a few projects in the response; the remaining project leads display as IDxxxxx when I select all records from the project table.

SELECT DISTINCT p.pkey,p.LEAD,u.display_name
FROM project p
INNER JOIN cwd_user u
on p.lead = u.user_name
GROUP BY p.pkey,p.LEAD,u.display_name
ORDER BY p.pkey DESC

select p.pkey as Proj_key, p.LEAD as Project_Lead, cu.email_address as Proj_Lead_Email, pc.cname as Proj_Cat
from project p
JOIN nodeassociation na ON na.SOURCE_NODE_ID = p.ID
JOIN projectcategory pc ON na.SINK_NODE_ID = pc.ID
JOIN cwd_user cu ON cu.user_name = p.LEAD
where na.SINK_NODE_ENTITY ='ProjectCategory'

Thanks in advance for your help!

1 answer

1 accepted

0 votes
Answer accepted
Andy Heinzer Atlassian Team Oct 09, 2019

Hi Richard,

I see that you are using Jira Server and looking to find all the project leads using a SQL query.   The problem with this approach is in part due to to a change recently in Jira server in regards to user_key values when accounts are created and in part to using the wrong field when trying to match up these users.  I believe this change was just implemented in Jira 8.4, there is some more background about it in GDPR changes in Jira: Jira user keys .

Back in 6.0, Jira server introduced the ability to rename user accounts.  When this happened, Jira added a new table called app_user.  This has just two fields, user_key and lower_user_name.  The user_key value is expected to never change in Jira, even if the account is renamed.  Initially Jira has been just selecting the first username as the userkey.  This made sense as a means to identify the user, and so many places in Jira that contain user_names are actually the user_key that just happens to have the same value.  So your query might have worked in the past, but it only would have returned accounts accurately that have not been renamed.  The lower_user_name field in this table will always change to correspond to the current value in cwd_user with the same field name.

Starting with Jira 8.4 when an account is first created in Jira, the user_key value that is generated is no longer using the accounts first username, likely because this value is a real pain to change in Jira, and GDPR regulations could consider it a personally identifying field.  Which means if this user files some kind of GDPR request you'd be required to alter the value here, something Jira should be avoiding now.   Which is why Jira Server has started switching to use a JIRAUSER10000, JIRAUSER10001, etc when creating accounts for the first time in newer versions.

That said I think you can tweak your query here.  Instead of trying to match directly to the cwd_user table, you'll need to first match that project lead field to the app_user.user_key field.  From there we can then find the usernames and display names, like so:

SELECT DISTINCT p.pkey,p.LEAD,au.lower_user_name,cu.display_name
FROM project p
INNER JOIN app_user au on p.lead = au.user_key
JOIN cwd_user cu on cu.lower_user_name=au.lower_user_name
GROUP BY p.pkey,p.LEAD,au.lower_user_name,cu.display_name
ORDER BY p.pkey DESC

and for the second query, same idea,

select p.pkey as Proj_key, au.lower_user_name as Project_Lead, cu.email_address as Proj_Lead_Email, pc.cname as Proj_Cat
from project p
JOIN nodeassociation na ON na.SOURCE_NODE_ID = p.ID
JOIN projectcategory pc ON na.SINK_NODE_ID = pc.ID
JOIN app_user au ON au.user_key = p.LEAD
JOIN cwd_user cu ON cu.lower_user_name = au.lower_user_name
where na.SINK_NODE_ENTITY ='ProjectCategory'

That should still be able to give you the user account information I think you are looking for here via SQL.

Cheers,

Andy

Hi @Andy Heinzer ,

Thanks a bunch for the explanation of the new approach, which make sense considering GDPR regulations, and updated SQL. I got what I was looking for! By the way, I'm on Jira 8.3.0.

Cheers,

Rich

Like Andy Heinzer likes this
Andy Heinzer Atlassian Team Oct 09, 2019

Thanks for clarifying. I need to update that KB, it was published a few months ago and doesn't currently indicate the version where this change happened.   I will go back and update this to help clarify.

Andy Heinzer Atlassian Team Oct 09, 2019

It seems that there is a dark feature for Jira 8.2 and 8.3 mentioned in that link I posted above that would allow your Jira site to default to using this new format for user keys.  By default this feature is turned on for all Jira Server versions 8.4 and higher.  So perhaps a Jira admin in your site enabled this.  In my own default 8.2.3 and 8.3.2 versions I don't see the new user_keys yet, but I haven't enabled that dark feature either.

Anyways, my SQL queries should work just fine in any version of Jira Server to date.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Demo Den Ep. 7: New Jira Cloud Reports

Learn how to use two new reports for next-gen projects in Jira Cloud:  Cumulative flow diagram and Sprint burndown chart. Ivan Teong, Product Manager, Jira Software, demos the Cumulative ...

260 views 1 2
Join discussion

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