list of users who don't have any tickets assigned

[Madhu_Mullangi]

I want to know the users who don't have any tickets assigned.

My Lead needs this to know who are free without any work.

If the users don't have any tickets under their name, I want the list. So that I will be able to use the resource by assigning some tickets to him.

I have found a query and ran it in Database but its not working.. Attached the screenshot for your reference..

Please help me in getting the output.

Thanks in Advance..

[Ravikiran]

Please let us know where we fire this SQL query, We are using Jira cloud 

[Antoine Berry]

Hi @Ravikiran ,

I do not think you can do that out of the box as you do not have access to the database. You would probably need an app such as sql for confluence. I have only tried it on server though (and working fine).

Antoine

[Madhu_Mullangi]

Hi @Antoine Berry 

Tried the query which you have sent and it is not involving some users..who don't have any assigned tickets in Unresolved status..

Can I make "RESOLUTION = UNRESOLVED" like this.. Just asking

Thanks in Advance

[Antoine Berry]

Hi @Madhu_Mullangi ,

This request will retrieve all the active users that do not have an open issue assigned for the specific project. 

SELECT DISTINCT cwd_user.display_name
FROM cwd_user
WHERE cwd_user.active = 1
AND cwd_user.ID NOT IN (
select DISTINCT cwd_user.ID
FROM cwd_user
JOIN jiraissue ON jiraissue.assignee = cwd_user.lower_user_name
JOIN project on project.ID = jiraissue.project
JOIN issuestatus ON issuestatus.ID = jiraissue.issuestatus
WHERE issuestatus.STATUSCATEGORY <> '3'
AND project.pkey = 'KEY'
)

Hope this helps.

Antoine

[Madhu_Mullangi]

Hi @Antoine Berry 

Ran the above query suggested by you..

It is giving the list of all the active users including who have the issues assigned.

I only want the list of users who don't have any open issues assigned.

Thanks in Advance

[Antoine Berry]

Hi @Madhu_Mullangi ,

I excluded the "Done" issues with

issuestatus.STATUSCATEGORY <> '3'  

 So you will retrieve users who are assigned to issue(s) in "Done" status category (i.e. green statuses).

If you want to retrieve users who are not assignee at all on any issue, remove this condition.

I just ran the query and it works fine.

Antoine

[Madhu_Mullangi]

Hi @Antoine Berry 

I think my explanation is not clear..

I have 200 users in my jira. At one point of time all have issues assigned. They have worked on issues and closed those..leave the past

Now some have issues under their name and some don't have..

I want the list of users who don't have any issues assigned which are in Unresolved resolution..

Even if the user have closed issues as a assignee, I don't want that user to be involved in the list..

Thanks in Advance

[Antoine Berry]

Exactly, that is why I did not take into account green statuses. If you want to exclude resolved issues instead, you can use this query : 

SELECT DISTINCT cwd_user.display_name
FROM cwd_user
WHERE cwd_user.active = 1
AND cwd_user.ID NOT IN (
select DISTINCT cwd_user.ID
FROM cwd_user
JOIN jiraissue ON jiraissue.assignee = cwd_user.lower_user_name
JOIN project on project.ID = jiraissue.project
WHERE jiraissue.RESOLUTION IS NOT NULL
AND project.pkey = 'KEY'
)

For example, if you have following issues in your project : 

KEY-1 / assignee : user1 / status : Resolved

KEY-2 / assignee : user1 / status : In Progress

KEY-3 / assignee : user2 / status : Resolved

user2 will be in the list, but not user1, because he has an unresolved issue assigned.

Hope that is clear enough.

Antoine

[Antoine Berry]

Are you sure these users are active ? 

Also, could you run this JQL query in Jira : 

project = KEY AND assignee = user AND resolution = Unresolved

using one of these users, and provide screenshots of :

• the result of this JQL query
• the result of the SQL query highlighting the user you used in the JQL (showing the SQL you used as well) ?

Antoine

[Madhu_Mullangi]

Yes, I am sure that the user is active..

Attaching the screenshots for your Reference.

[Madhu_Mullangi]

It is also chowing up some names, Who actually have some issues..

[Antoine Berry]

I see, I do not understand what is wrong. This query works fine on my instance. Could you try to run this query and screenshot the result : 

select DISTINCT cwd_user.display_name
FROM cwd_user
JOIN jiraissue ON jiraissue.assignee = cwd_user.lower_user_name
JOIN project on project.ID = jiraissue.project
WHERE jiraissue.RESOLUTION IS NOT NULL
AND project.pkey = 'COP'

This should return all the users who have an issue assigned that is not resolved.

Antoine

[Antoine Berry]

Actually can you run this simple query : 

select jiraissue.RESOLUTION,
jiraissue.ASSIGNEE
from jiraissue
JOIN project on project.ID = jiraissue.project
JOIN cwd_user on jiraissue.assignee = cwd_user.lower_user_name
Where project.pkey = 'COP'
and issuenum = 58

This will probably explain why the trouble.

Antoine

[Tuncay Senturk]

Hello,

The SQL query you attached seems to be correct. What is the result or error when you run this query?

[Madhu_Mullangi]

Hi @Tuncay Senturk 

I have ran the query and attached the screenshot of the result.

Thannks in advance.