Access cwd_user_attributes table via add-on? Edited


I want to run sql queries on the tables cwd_user and cwd_user_attributes to retrieve users' last login dates as described here.

To run the queries, I tried 3 different sql add-ons (using JIRA Software 7.4.0, JIRA Server):

- Home Directory and DB Browser for JIRA suggests cwd_user in a drop down menu, but says "invalid object name cwd_user"

- SQL Reporter for JIRA fails with Java NullPointerException

- SQL for JIRA Driver: Convert JQL to SQL provides a SQL console that actually works, but it only has e.g. a table called USERS with columns username, fullname and email. But it does not find the tables cwd_user or cwd_user_attributes, which I need. (Error message: Table "CWD_USER" not found; SQL statement: select * from cwd_user)

What am I doing wrong?
Can I access those tables via add-on or what do I need to do?

1 answer

1 accepted

0 votes
Answer accepted

I got a solution. The 3rd add-on "SQL for JIRA Driver" released a new version (5.2.0) where you can now select from admin.userlogininfo. It needs a username as input parameter. So in order to get a list off all users with their last login, I run this query:

SELECT a.username, u.fullname, a.logincount, a.lastlogin
FROM users u, admin.userlogininfo a
WHERE a.username = u.username
 AND a.logincount > 0
ORDER BY lastlogin

Note that the table order at the FROM statement is important! As this results in


Suggest an answer

Log in or Sign up to answer
Community showcase
Published Mar 13, 2019 in Marketplace Apps

Marketplace Spotlight: Marketing apps for Confluence to keep your teams working on the same page


249 views 0 6
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you