Access cwd_user_attributes table via add-on?

[Andreas P]

Hi,

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?

[Andreas P · Accepted solution]

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

FROM PUBLIC.USERS U
INNER JOIN ADMIN.USERLOGININFO A