nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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..

Accepted answers

All comments

Tuncay Senturk _Snapbytes_

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 _Snapbytes_

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

Thannks in advance. Error DB.PNG

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

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

Ravikiran_ail0es

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server