Creating a list of idle users from the JIRA database

[David Finney]

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?

[Norman Abramovitz]

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]

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.

[Mike Bertelsen]

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.