Looking to query the Jira DB for a list of all users and their last comment date (on any issue)

[Aidan Satterwhite]

Hello,

We're looking to reclaim some license keys from unused accounts, but we utilize a plugin that lets users comment via email, so users who are active on Jira don't necessarily login often. We thought that we could identify users who aren't active by their last comment date instead of last login date, and have been working hard to write a SQL query to pull this information from the Jira DB, but we're not entirely sure in which table the last comment date would be stored. Can someone please point me in the right direction, or help me identify a better way to determine unused licenses in our situation?

Thank you!

[Nic Brough -Adaptavist- · Accepted solution]

Welcome to the Atlassian Community!

Jira does not directly story the "last comment date".  It stores a list of all comments with the date they were made, so you'll have to read for all of them and work out which one is the latest based on the date it was posted.  All comments are kept in the Jiraaction table.

[Aidan Satterwhite]

Thanks for pointing me in the right direction. I was able to get a workable export using the following query:

---

\copy (select m.child_name AS Username, u.display_name, j.actiontype, j.created, u.active from cwd_membership m, cwd_user u, jiraaction j where u.id = m.child_id and m.child_name = j.author and u.display_name like ('[REDACTED]') and m.child_name NOT IN ('[TEST_USER1]','[TEST_USER2]') and u.display_name NOT LIKE ('([REDACTED])%') and u.active=1 order by created desc) to '/tmp/results.csv' (format csv,header,delimiter ',')

---

I believe the query needs to be run as a single line like above to correctly export, at least in my experience. Appreciate the help!