It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Creating a list of idle users from the JIRA database

I define idle users as those who haven't logged in, created or commented on an issue since a particular point of time. I need to create the list of them. I understand that it should involve the following tables:

  • cwd_user
  • cwd_user_attributes
  • jiraissue
  • jiraaction

I am confused about the logic. Should I create individual lists of entries for each table for those users whose latest date/time of action is before my threshold date and then take the common subset of those lists?

2 answers

This is the start of the query that works on Sql Server 2008 r2. It is just returning the user and a timestamp. I would use a union to combine the data. If all you need is a data verses a date and time, then I would make sure to convert the timestamp to just a date.


select * FROM
(select CU.user_name AS username,
DATEADD(ss,CAST(CAST(attribute_value as varchar(255)) AS NUMERIC)/1000, '01/01/1970') AS LastAccess
from JiraProduction.JiraSchema.cwd_user AS CU
INNER JOIN JiraProduction.JiraSchema.cwd_user_attributes AS CUA ON (CU.ID = CUA.ID)
where attribute_name = 'lastAuthenticated'
UNION
select UPDATEAUTHOR,UPDATED from JiraProduction.JiraSchema.jiraaction) AS TT

The hard part is converting the cw_user_attribute to a date/time value which is database dependent. The time component still needs to be adjusted by the timezone to get a correct value.

Hi Norman,

I am afraif the condition 'CU.ID = CUA.ID' is not corrent. It should be 'CU.ID = CUA.user_id'. So the latest version of the query is :

select * FROM

(select CU.user_name AS username,

DATEADD(ss,CAST(CAST(attribute_value as varchar(255)) AS NUMERIC)/1000, '01/01/1970') AS LastAccess

from [jira].[dbo].[cwd_user] AS CU

INNER JOIN [jira].[dbo].[cwd_user_attributes] AS CUA ON (CU.ID = CUA.user_id)

where attribute_name = 'lastAuthenticated'

UNION

select UPDATEAUTHOR,UPDATED from [jira].[dbo].[jiraaction]) AS TT

I think it still have some issues as the username is NULL for some rows.

I use a manual process to get these results.

I set the user filter to "All"

Then copy the output to an Excel spreadsheet

Next i remove columns I don't need leaving username and Login Details

I copy what's left into Textpad using its find/replace regular expressions to remove extra lines and sort the data.

Then I Book Mark all rows that show an access in the current year, Then use the "delete bookmarked lines" option.

Next I pull out just the username and add it to a scripted process that removes users in the list.

If the process can't remove the user it removes their groups so the usernames can't be used to log in and they don't count toward the license count.

 

Suggest an answer

Log in or Sign up to answer
Community showcase
Published in Off-topic

Atlassian Community Guide to A+ Articles

Stop: Articles and permission Article-writing permissions are given to Leaders, Marketplace Vendors, Solutions Partners, Atlassian Team members, and those who are Level 4 + above. But for any aspir...

67 views 7 6
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you