Query to get the list of unmapped permission schemes,notification schemes and issuetype schemes

Hi All,

Can any one please share the query to fetch the unmapped permission schemes,notification schemes and permission schemes.

Thanks & Regards,

Jayasingh

6 answers

This widget could not be displayed.

please tell me the query to find unmapped groups from the project in Jira?????

This widget could not be displayed.

You can't look for a negative.

You need to get the full list of all groups, then the groups used in whereever you are looking and then subtract the list of used groups from the full list. The remainder will be the unused groups.

Note that you also need to consider group-picker fields, filters and gadgets if you want to delete groups safely.

This widget could not be displayed.

I am having 10999 groups. How i can manually find the groups which is not mapped with any permission scheme??

Please help me to find some way to cleanup the Groups which are not mapped with any permission scheme.

Please, read the responses you are being given.

Asking the same question over and over without bothering to take account of what you are being told, and not adding any new information is not going to get you anywhere.

hello Nic,

I am not asking the same question again and again.. We need a sql query to delete all the groups which is not mapped with any permission scheme.

I am having a single question .. there is no relation between CWD_GROUP table and Node association table.. then how we will be able to make a query to fetch unused groups?.

For that you have answered "to get the full list of all groups, then the groups used in whereever you are looking and then subtract the list of used groups from the full list. The remainder will be the unused groups".

Its take huge time to delete.. so i have asked any other way is there to handle...

Any way thanks for your prompt response.

But for sure once i find solution will share the same:)

Thank you so much..

I'm afraid you have asked this over and over, in several places, and until today, you do not appear to have read any of the responses. Instead, you've posted the same question phrased in a slightly different way, either here, or on another thread.

Going back to the question, the answer has not changed. You need to get the list of all groups and subtract the used groups from it.

You have not yet really decided what a "used" group is yet either. Is it really just "used in permission scheme"? That is simple (and you've been pointed at SQL that can help you with it, albeit not the exact answer - we tend to ask you to think for yourself as you'll learn more if than if you just parrotted our SQL) - read the table schemepermissions for lines with perm_type of "group" and it will name the groups.

Or do you need to consider the other places a group can be used? Most of these *might* be indirectly used to grant access as well. Project roles, group custom fields, security schemes, gadgets, filters and workflows. I can't tell you whether you need to look for these, you need to analyse your system to work it out.

Hi Nic,

Developed query to fetch groups which is not mapped with permission schemes.

the query is,


SELECTdistinct group_name FROM CWD_GROUP
where group_name not in
(SELECT distinct perm_parameter FROM schemepermissions where perm_type='group');

Thanks for your support in finding schemepermissions teble:):)

This widget could not be displayed.

Hi Nic,

I have developed query to fetch groupname which is unamapped with permission schemes.

Query:

SELECTdistinct group_name FROM CWD_GROUP
where group_name not in
(SELECT distinct perm_parameter FROM schemepermissions where perm_type='group');

here, schemepermissions and cwd_group are the two tables.In the schemepermission table we have perm_parameter field which shows the groupnames which are mapped with any permission schemes.In CWD_GROUP table we can see groupnames whichever we created even if it is not mapped with any permission schemes.So by using these both table we found a query to fetch the groupnames which is not mapped with any permission schemes.

So this is the another way to fetch groupnames which is not mapped with any permission schemes:):)

Thank you so much for guiding me to find the unmapped groups.

This widget could not be displayed.

Hi Nic,

I have developed query to fetch groupname which is unamapped with permission schemes.

Query:

SELECTdistinct group_name FROM CWD_GROUP
where group_name not in
(SELECT distinct perm_parameter FROM schemepermissions where perm_type='group');

here, schemepermissions and cwd_group are the two tables.In the schemepermission table we have perm_parameter field which shows the groupnames which are mapped with any permission schemes.In CWD_GROUP table we can see groupnames whichever we created even if it is not mapped with any permission schemes.So by using these both table we found a query to fetch the groupnames which is not mapped with any permission schemes.

So this is the another way to fetch groupnames which is not mapped with any permission schemes:):)

Thank you so much for guiding me to find the unmapped groups.

So, you got the list of groups, then subtracted the groups in the permission schemes. Glad you got there in the end.

However, what about roles, group fields, filters, gadgets, notifications, workflows etc? I do hope you're not just planning to remove groups based on permissions, because there's LOTS of other places they're potentially used.

Yes we are considering all those.

Anyway thank you so much for your help in finding the table:)

Hello Abinaya Can you help me in figuring out a sql query to fetch all permission schemes where 'Delete issue' permission is not set to empty? Thanks Shradha

This widget could not be displayed.

Hi All!

Please, and what about  the query to fetch the unmapped issuetype schemes?

It's a negative question - you can't look for what isn't there.  You will need to read for all the issuetype schemes, then subtract the used ones from it.

Or, look in the UI, it's far easier, and you can use it to delete the unused ones (which you must not do in SQL)

Nic, Hi!

I meant that I need to get list of projects that use default issuetype scheme, But this case I decided:

select p.id, p.pname from project p
where p.id NOT IN (select cc.project from configurationcontext cc where cc.customfield='issuetype' and cc.project is not NULL)

then, I need bulk change default issue type scheme on projects. How to do this, I don't know

There's no bulk-change for this stuff, you need to work through it one at a time.  It's important that you do it this way as changing schemes may result in needs to update projects and it will need to ask you questions.

You'll still need to answer the migration questions manually.

sorry, didn't understood smile

I resolved with: https://bobswift.atlassian.net/wiki/display/JCLIP and 

jira --action updateProject --project "zjiracli" --issueTypeScheme "10000"

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

165 views 1 3
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you