nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL query on Jira user properties

Tim Brown10

In our Jira environment (v4.3) we use the user properties feature to store metadata against a user. We'd like to be able to report on user's by these properties.

Can someone advise which table stores the user properties & what key it uses to link to the cwd_user table?

Thanks in advance.

Tim

Accepted answers

Bjarni Thorbjor

Hi Tim,

I think this is what you are looking for:

select * 
  from propertyentry pe, 
    propertystring ps,
    external_entities ee
  where pe.ID = ps.ID
    and ee.ID = pe.ENTITY_ID
    and PROPERTY_KEY like 'jira.meta.%'

But you should of course rather use the public interface, something like:

PropertySet properties = userPropertyManager.getPropertySet(user);

Hope this helps,

-Bjarni

All comments

Norman Abramovitz

If I understand correctly, the table should be CWD_USER_ATTRIBUTES.

Tim Brown10

Hi,

Thanks for the suggestion, but it must be a different table from this one - I added a new property to a user (Admin > Users > Edit properties) and cannot see it in this table.

Any other suggestions?

Tim

Bob Swift

If you are using crowd, it is cwd_user_attribute and we use this in a query to get information all the time. I am not sure JIRA uses the crowd support for its attributes.

Bjarni Thorbjor

Hi Tim,

I think this is what you are looking for:

select * 
  from propertyentry pe, 
    propertystring ps,
    external_entities ee
  where pe.ID = ps.ID
    and ee.ID = pe.ENTITY_ID
    and PROPERTY_KEY like 'jira.meta.%'

But you should of course rather use the public interface, something like:

PropertySet properties = userPropertyManager.getPropertySet(user);

Hope this helps,

-Bjarni

Edmond Ho

Thanks for the heads up! I ended up modifying it to join the cwd_user table so I can view the users that it's applied to:

select pe.property_key, cu.user_name, cu.email_address from propertyentry pe JOIN cwd_user cu ON pe.entity_ID = cu.id where property_key LIKE "%jira.meta%" order by cu.email_address;

unknown

In newer Jira Versions it's a different Table, not cwd_user it's now app_user. Here is the Query:

select pe.property_key, ps.propertyvalue, apu.user_key
from propertyentry pe
JOIN app_user apu ON pe.entity_ID = apu.id
  Left JOIN propertystring ps ON ps.id = pe.id
where property_key LIKE '%jira.meta.%'

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Unanswered
Groups
Help

Find more posts tagged with

jira