SQL query on Jira user properties

[Tim Brown]

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

[Bjarni Thorbjornsson · Accepted solution]

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;

[Christoph Lasecki]

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

[Norman Abramovitz]

If I understand correctly, the table should be CWD_USER_ATTRIBUTES.

[Tim Brown]

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 OSS (Bob Swift Atlassian Apps)]

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.