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

David Finney Jul 19, 2013

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

1 vote
Norman Abramovitz Jul 19, 2013

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.

David Finney Jul 21, 2013

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.

0 votes
Mike Bertelsen Nov 13, 2014

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
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Posted in United States

Topic Tuesday: Agile

Another week, another Topic Tuesday!  Agile has been on my mind a lot lately because I've seen good implementations and bad implementations, but now as a Jira admin it matters more to me whether...

14 views 0 0
View post

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