Access cwd_user_attributes table via add-on? Edited

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?

1 answer

1 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

FROM PUBLIC.USERS U
INNER JOIN ADMIN.USERLOGININFO A

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Jul 10, 2018 in Marketplace Apps

If you’re an Atlassian app developer, you’ll want to know about Atlas Camp!

This September 6-7, hundreds of Atlassian App developers will flock to Barcelona Spain to build skills, discover product roadmaps, meet face-to-face with the Atlassian team, and learn how to extend t...

176 views 0 4
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