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

This widget could not be displayed.

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
Community showcase
Published Aug 22, 2018 in Marketplace Apps

How a Marketplace app tech team is achieving gender diversity

Hello! My name is Genevieve Blanch, and I'm the Marketing Manager at RefinedWiki, creators of apps to give teams the tools to customize Atlassian platforms. Currently, 44% of the tech team at Re...

487 views 3 18
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