Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

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

Finding the inactive users with respective project

Is there any mysql for getting inactiveusers with respetive projects

4 answers

The query should look like this:

select user_name, ua.attribute_value LAST_LOGIN_IN_MILLIS
from cwd_user u, cwd_user_attributes ua
where u.id = ua.user_id and ua.attribute_name ='login.lastLoginMillis'
and to_number(ua.attribute_value) < (select max(to_number(attribute_value))-15552000000  -- minus 180 Days
     from cwd_user_attributes
     where attribute_name ='login.lastLoginMillis')

Please note this query is for Oracle you need to adapt it to MySQL. It shows all User with last Login 180 days before today (assuming someone logged in today)

0 votes

You need to define "inactive" users somehow. I've got users who use systems for reading - they might be considered inactive because they don't contribute, but for us, definitely are NOT inactive.

Would also help if you told us which system you are looking at? Is it Jira?

Repeating the same question is not helpful.

Yes, Jira, thank you. Now, again, what do you mean by "inactive"? And then "respective projects"?

Hi Nic,

Thanks for quick reply...

I need mysql query where we get inactive users asscosiated with respective projects data in JIRA.

Hi Nic,

Inactive means users who have not logine jira for a period of SIX months.Say let us assume we have 100 projects in jira in this projects we have inactive projects in this inactive projects we have inactive users.so i need a query which can get inactive project and users asscioated with this project.

Thanks,

Harsha

Ok, that's better. This is a task you probably want to split into two parts.

An inactive user is someone who hasn't logged in for 6 months. In the UI, an admin can simply look at the user's profile to see their last login date. The activity gadget on their profile will even tell you the last time they touched data as well. From memory, in SQL, look at the table CWD_USER to see the last login date.

Once you've id'd an inactive user, then you need to think about "associated with project" - what is this association? Do you mean they're in a role in the project? Something else? I just remove all groups and roles from "inactive" users wholesale, it's not really worth working out the details...

Like Farida Musayeva likes this

I would point out that Nic is correct. If the definition is "hasn't signed in in 6 months...", it is really irrelevant to be concerned about the specific projects.

This query looks like it only lists those who have already been formally identified as inactive.

That's correct, it will only find users already as inactive.

Suggest an answer

Log in or Sign up to answer
TAGS

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