filter for parent/subtask or SQL query

[Cynthia K. McNeil]

I need to write a filter that notes differences between a parent issue and subtask where fixversion is not equal. Is there a way to write a filter. Or do I need to write a database query. If so, does anyone have an example of a similar SQL query?

[JamieA · Accepted solution]

> Jamie, I found your link on hacking the built in scripts. I don't have that path in my directory at all. How else would built in scripts be called

It won't be on your directory, it will be in the jar.

If you put the script on your filesystem you need to make the directories corresponding to the package under web-inf/classes... you may need to restart jira the first time, can't remember.

> I want to add that the resolution cant be resolved or canceled

Something like this, where 7 and 8 correspond to the resolution IDs for the above:

def query = builder.where().issueTypeIsSubtask().and().not().resolution("7", "8").buildQuery()

If you set up an IDE as per https://studio.plugins.atlassian.com/wiki/display/GRV/Script+Runner#ScriptRunner-DebuggingandCodingwithIDEA you will find it a lot easier.

But you can also create queries this way:

JqlQueryParser jqlQueryParser = ComponentManager.getComponentInstanceOfType(JqlQueryParser.class) as JqlQueryParser
def query = query = jqlQueryParser.parseQuery("assignee is abc")

Then you can just paste in the query from the advanced edit screen in the issue navigator.

Here is a full canned script including what I did above: https://gist.github.com/1690170

The project parameter doesn't do anything at the moment, up to you if you want parameters or not.

> Thanks this is a really great thing...I have lots of things that i can write in groovy

Cool, the more converts to the cause the better!

[Cynthia K. McNeil]

Agreed...thank you!

[JamieA]

BTW Answers doesn't really work well when it's used in a back and forth way... I think the original question is answered... would be good if you could create a new question for anything else. Also helps people when searching.

cheers, jamie

[JamieA]

> are you getting the points

Yes, that's lovely, thanks.

Your code doesn't look valid...

def cf = customFieldManager.getCustomFieldObjectByName("TextFieldA")

if (issue.getCustomFieldValue(cf) != issue.parentObject.getCustomFieldValue(cf)) {...}

should work.

[Cynthia K. McNeil]

Jamie are you getting the points?? anyway, now I am trying to get the customfield values. the code you have doesnt' work for me. I always did it in this way

if (issue.getCustomFieldValue(cf.Manager.getCustomFieldObject("customfield_10120")) != issue.parentObject.getCustomFieldValue(getCustomFieldObject("customfield_10120")))

And that doesn't work either. Confused.

[JamieA]

priority is issue.priorityObject.name

Points... if you think an answer is helpful click the up arrow, if correct click the tick. If a comment is helpful click the thumbs up icon!

Thanks, I am expecting the motherlode of points!

[Cynthia K. McNeil]

Jamie,

I wil give you all the points in the world....how :)

Ok, so now I need to access priority. i can't just drill to the issue object. It doesn't work. if I just plug in priority. Will give the canned script thing a try. It woulld be easier to run it if it was built in. thanks!

[JamieA]

> how do I make this script a built in script

Well you need to implement the CannedScript interface - easiest thing is to copy the simplest of the existing ones and just chuck your code in the doScript method or whatever it's called. All the built-in script sources are in both the binary and source distro.

[JamieA]

> Thanks so much. I really appreciate this

That's fine, but can you give me some points ;-)

My original script worked, but it printed the results to the screen. Your "new File" can't be in the "collect", otherwise the same file will keep getting created and written to. The collect collects the last thing returned, which is null for file.append().

Example below with writing to a file, showing the components, and getting a CF:

package examples

import com.atlassian.jira.ComponentManager
import com.atlassian.jira.jql.builder.JqlQueryBuilder
import com.atlassian.jira.issue.search.SearchRequest
import com.atlassian.jira.issue.search.SearchResults
import com.atlassian.jira.web.bean.PagerFilter
import com.atlassian.jira.issue.search.SearchProvider
import com.atlassian.jira.issue.Issue

def componentManager = ComponentManager.getInstance()
SearchProvider searchProvider = componentManager.getSearchProvider()
def customFieldManager = componentManager.getCustomFieldManager()
JqlQueryBuilder builder = JqlQueryBuilder.newBuilder()

def cf = customFieldManager.getCustomFieldObjectByName("Name of custom field")

// enter additional terms here to be more selective on what you want to filter on
def query = builder.where().issueTypeIsSubtask().buildQuery()

SearchRequest sr = new SearchRequest(query)
SearchResults results = searchProvider.search(sr.getQuery(), componentManager.getJiraAuthenticationContext().getUser(), PagerFilter.getUnlimitedFilter())
new File("c:/temp/foo.txt").withWriter {Writer w ->
    results.issues.each {Issue issue ->
        if (issue.fixVersions.sort() != issue.parentObject.fixVersions.sort()) {
            w.write("Issue: ${issue.key} has fixVersions different from the parent. Components: ${issue.componentObjects*.name}. ")
            w.write("Custom field: ${cf.name} : ${issue.getCustomFieldValue(cf)} ")
            w.write("\n")
        }
    }
}

[Cynthia K. McNeil]

import com.atlassian.jira.ComponentManager
import com.atlassian.jira.jql.builder.JqlQueryBuilder
import com.atlassian.jira.issue.search.SearchRequest
import com.atlassian.jira.issue.search.SearchResults
import com.atlassian.jira.web.bean.PagerFilter
import com.atlassian.jira.issue.search.SearchProvider
import com.atlassian.jira.issue.Issue

def componentManager = ComponentManager.getInstance()
SearchProvider searchProvider = componentManager.getSearchProvider()

JqlQueryBuilder builder = JqlQueryBuilder.newBuilder()

// enter additional terms here to be more selective on what you want to filter on
def query = builder.where().issueTypeIsSubtask().buildQuery()

SearchRequest sr = new SearchRequest(query)
SearchResults results = searchProvider.search(sr.getQuery(), componentManager.getJiraAuthenticationContext().getUser(), PagerFilter.getUnlimitedFilter())
def rt = results.issues.collect {Issue issue ->
    if (issue.fixVersions.sort() != issue.parentObject.fixVersions.sort()) {
    file = new File("c:/foo.txt")    
    file.append("Issue: ${issue.key} has fixVersions different from the parent")
    }
}
rt

[Cynthia K. McNeil]

Hi Jamie,

Yes, I added a redirect to a file. I will include the code below. I also want to add other system fields like components, priority and two custom fields. Can I same snippet for custom fields? Also, how do I make this script a built in script?

Thanks so much. I really appreciate this.

[JamieA]

You mean you ran my script below? And it gave you like: [null, null] or something?

[Cynthia K. McNeil]

Ok, so I can use groovy. Can i add other conditions, for example components and custom fields? Is there any way to return to a file that can be read? I run the query and get many nulls and that's it.

Thanks so much!

[JamieA]

This is reasonably hard in SQL, but easy enough with the API. If you have the script runner plugin you can do it with the following script:

package examples

import com.atlassian.jira.ComponentManager
import com.atlassian.jira.jql.builder.JqlQueryBuilder
import com.atlassian.jira.issue.search.SearchRequest
import com.atlassian.jira.issue.search.SearchResults
import com.atlassian.jira.web.bean.PagerFilter
import com.atlassian.jira.issue.search.SearchProvider
import com.atlassian.jira.issue.Issue

def componentManager = ComponentManager.getInstance()
SearchProvider searchProvider = componentManager.getSearchProvider()

JqlQueryBuilder builder = JqlQueryBuilder.newBuilder()

// enter additional terms here to be more selective on what you want to filter on
def query = builder.where().issueTypeIsSubtask().buildQuery()

SearchRequest sr = new SearchRequest(query)
SearchResults results = searchProvider.search(sr.getQuery(), componentManager.getJiraAuthenticationContext().getUser(), PagerFilter.getUnlimitedFilter())
def rt = results.issues.collect {Issue issue ->
    if (issue.fixVersions.sort() != issue.parentObject.fixVersions.sort()) {
        "Issue: ${issue.key} has fixVersions different from the parent"
    }
}
rt

[Cynthia K. McNeil]

Nicely Done....that works! Also, can you either point me to the canned script documentation or tell me where it is? do you think that that is best way to give a script like this management and tell them to run it? Is there a better way. .....points all around....:)

[JamieA]

Well, only admins can access the script runner admin panel... and I wouldn't let my managers be admins for all the tea in China.

The best option is a jira report plugin, but writing that is more complex than the script you've got.

At some point I hope to extend the script runner to reports....

[Cynthia K. McNeil]

It ok, I can run them for the others. I would very much like to see extending the script runner to reports. But I can't even find the canned interface in my directory. i found the documentation, but not the built ins although they exist on verison of JIRA (4.1.1)

[Cynthia K. McNeil]

Jamie, I found your link on hacking the built in scripts. I don't have that path in my directory at all. How else would built in scripts be called? Also, can you show me how to use the build query to be more robust

// enter additional terms here to be more selective on what you want to filter on
def query = builder.where().issueTypeIsSubtask().buildQuery()

I want to add that the resolution cant be resolved or canceled.

Thanks this is a really great thing...I have lots of things that i can write in groovy:)