scriptrunner automated report

[Jennifer Ward]

I have written a script that gets all the detail I need but at this point it has to be manually run and then saved and then re-opened in excel..  Here is the script (below) and the final objective is to utilize this to generate output automatically each quarter.  Basically I am totaling hours around field selections on issues, but need this in a report/output that does not have to be manually run and generated.  What is the best approach for this utilizing scriptrunner or a combo of scriptrunner and delivered Jira functionality and free automation for jira lite..  Not looking to purchase another plugin

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.search.SearchProvider
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.web.bean.PagerFilter
def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchProvider = ComponentAccessor.getComponent(SearchProvider)
def issueManager = ComponentAccessor.getIssueManager()
//def user = ComponentAccessor.getJiraAuthenticationContext().getUser()
def user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser()
String field1 = "Client";
String field2 = "OIG Strategic Goal";
//TODO: Please change field 1 and field 2 name to proper names according to your actual settings
List<String> value1 = new ArrayList<String>();
value1.add("Audit");
value1.add("Investigations");
value1.add("Management");
value1.add("ODS");
List<String> value2 = new ArrayList<String>();
value2.add("1- Safety and Security: Strengthen USDA’s ability to implement and improve safety and security measures to protect the public health, as well as agricultural and Departmental resources");
value2.add("2- Integrity of Benefits: Detect and reduce program vulnerabilities and deficiencies to strengthen the integrity of the Department’s programs");
value2.add("3- Workforce: Recruit, develop, and maintain a highly qualified and diverse workforce while fostering a work environment that promotes productivity, innovation, excellence, and employee satisfaction");
value2.add("4- Improvement Initiatives: Provide USDA with oversight to help it achieve results-oriented performance");
//TODO: Set the options of two fields accordingly.
StringBuffer result = new StringBuffer();
result.append(field1)
result.append(",")
result.append(field2)
result.append(",Keys,Hours")
result.append("</br>")
for(String first_value: value1){
for(String second_value: value2){
String jql = "\"" + field1 + "\"=" + "\""+ first_value + "\" and \"" + field2 + "\"=\"" + second_value + "\"";
// result.append(jql);
// result.append("</br>")
def query = jqlQueryParser.parseQuery(jql);
def results = searchProvider.search(query, user, PagerFilter.getUnlimitedFilter())
log.debug("Total issues: ${results.total}")
long total_log = 0;
if(results.total>0){
result.append("\"")
result.append(first_value)
result.append("\"")
result.append(",")
result.append("\"")
result.append(second_value.replace("'","\'"))
result.append("\"")
result.append(",")
result.append("\"");
results.getIssues().each {documentIssue ->
log.debug(documentIssue.key)
result.append(documentIssue.key)
result.append(",")
def issue = issueManager.getIssueObject(documentIssue.id)
def time_spent = issue.timeSpent
if(time_spent!=null) {
total_log = total_log + time_spent
log.debug(total_log)
}
}
if(result.toString().charAt(result.length()-1)==","){
result.setLength(result.length()-1)
}
result.append("\",")
result.append(total_log/3600)
result.append("</br>")
}
}
}
return result.toString()

[Aron Gombas _Midori_]

If you eventually changed your mind, give a try to the Better Excel Exporter app, because:

1. It is integrated with ScriptRunner (to reuse your skills).
2. It is integrated with Automation for Jira (for automatic report generation).
3. It is integrated with Tempo (to collect the report's source data).

It could a powerful tool set for your use case.

[Peter-Dave Sheehan]

I'm not sure if such a script would have been my first idea ... but if it works for you and you want to keep it, you could run it as a global service (see doco) and add a few lines of code to email the output instead of just outputting a string. You could use some simple html/table to make it more appealing in the email body.

Another idea came to mind... don't think if that's a good out, it's a bit out of the box.

You could create a separate project (or issue type) where you can create a few issues that represent the combination of field1 and field2 that you want to report on, and then have a custom scripted field that uses the same JQL to calculate your aggregate value.

Then those placeholder issues can be searched/filtered-on or placed in a dashboard and be constantly available. You can have a subscription to send the results to the right people quarterly.

[Jennifer Ward]

That second suggestion is actually what I was leaning to, but haven't used a scripted field before and wasn't sure of how to approach it..  I think I would have to break the script out so as to pull each combo (Client and Goal) separately though?  Then use those scripted fields (would be 16 in total) within the filters how you are saying...  Does that sound like what you were aiming for too?

[Peter-Dave Sheehan]

My understanding from your script is that you would need only 1 scripted field for something like "Total Hours". Maybe a second if you need to list all the issue keys.

So new project RPT with only 3/4 fields on the screens

• Client
• OIG Strategic Goal
• Total Hours
• Issues

Then you create your 16 issues:

• RPT-1 Client = Audit & OIG Strategic Goal = "1- Safety and Security ..."
• RPT-1 Client = Audit & OIG Strategic Goal = "2- Safety and Security ..."
• ...
• RPT-16 Client = ODS & OIG Strategic Goal = "4- Improvement Initiatives ..."

Then your scripted field would follow this Adaptavist Script Library example with the jql being something like:

def customFieldManager = customFieldManager.customFieldManager
def clientCf = customFieldManager.getCustomFieldObjectByName("Client")
def goalCf = customFieldManager.getCustomFieldObjectByName("ODS & OIG Strategic Goal")
//or use the customfield id if you know it
//def goalCf = customFieldManager.getCustomFieldObject(123456)
def clientVal = issue.getCustomFieldValue(clientCf)
def goalVal= issue.getCustomFieldValue(goalCf )
def jqlSearch = """
   Client = $clientVal AND "ODS & OIG Strategic Goal" = $goalVal
"""

One thing to mention is that scripted custom fields don't store their values in the db. They are calculated on the fly when the issue is indexed or accessed.  They are not re-calculated when other issues (such as those that match the jql) are updated.

But when you view the issues, either in jira, in a filter or in the dashboard, it will be calculated fresh each time.

[Jennifer Ward]

Thanks so much for your insight..  I went a bit of a different route, but got the output I needed..  If only Tempo would just provide better reporting features!  Thanks again!