issuefunction commented not inRole - find comments visible for all user

[Carsten Schäfer]

Hi,

we are working in comment visibilty with four, individual project roles in our JIRA instance (for e.g. role1,role2,role3,role4) and system role "all Users".

I am searching for issues which not contain at least one comment which is visible for "all Users". (=all issues without a comment visible for everyone)
Using issuefunction in commented("role role1, etc.") did not suit my needs. 

How can I do that, using issuefunction in commented? 
 
Thank you in advance!

KD, Thorsten

[Carsten Schäfer]

I tried out SQL for JIRA.

Unfortunately my sql scripts does not work. My account can not access table issuecomments, neither as JIRA admin! Simple select on issuecomment fails, because issuecomment isnt available.

On the other hand I did not prefer SQL for JIRA, because every user can access things which I can prevent?
Furthermore I like to subscripe a filte abo, what is afaik with JIRA sql not possible?

[Pablo Beltran]

The "missing tables" issue it has been fixed in the 2.0.3 version which has been released few hours ago.

All the SQL for JIRA queries are resolved against the JIRA API according to the user performing the SQL query and his permissions on JIRA. So users can view the same data they already view from the JIRA web application only.

Your MySQL scripts will not work because SQL for JIRA is based on H2 rather than MySQL. However, it would be easy to use SQL for JIRA from inside a Scriptrunner: SQL for JIRA supports a easy and standard public API and you are able to obtain javax.sql.Connection objects in the standard way just like any other relational database like MySQL, PostgreSQL, etc.

I'm afraid in regard to getting the point for "filter abo". What does it mean?

[JamieA]

Currently you can't use that to search where this is no role level... (ie All Users) - vote for https://productsupport.adaptavist.com/browse/SRJIRA-710

You may need to try the SQL way, even if you don't enjoy it.

[Pablo Beltran]

Did you hear about SQL for JIRA? ScriptRunner looks like the best choice as you are already using it, but if it does not meet your needs then SQL for JIRA might help. The ROLEID on the  ISSUECOMMENTS  tables is the role required to view the comment, so the condition:

ROLEID  is NULL

will fetch public comments only.

Advice: The SQL that resolves this  is not trivial but if you are an advanced SQL user then this is something that you will definitively enjoy. Otherwise, do not try.