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?
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
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...
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!
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